Philly Tide-

Yes, those formulas look fine- thank you.

I added a second line right after your formula that varies
the lookback number and the summation to be a variable
(using K1 and M1 below)
instead of a fixed number.

That way you can make a 2x2 table in Excel
(lookback values as a row and summation values a column)
and see the results of changing the variables.

Or use the solver add-in of Excel.
(If you have never used solver, take a few minutes to check it out.

Column E
=IF(B14=MAX(B14:B20),1,0) - (Philly Tide's)
=IF(B14=MAX(b14:OFFSET(B14,$K$1,0,1,1)),1,0) (using a variable)

Column F
=IF(C14=MIN(C14:C20),1,0)
=IF(C14=MAX(c14:OFFSET(C14,$K$1,0,1,1)),1,0)

Column G
=SUM(E14:E20)
=SUM(E14:OFFSET(E14,$M$1,0,1,1))

Column H
=SUM(F14:F20)
=SUM(F14:OFFSET(F14,$M$1,0,1,1))

NOTE:

(this is assuming the data is sorted with the latest (newest) first
and the oldest last and in this case that the data starts (say, April 2023).
in the 14th row)

Mechanical Investing FAQ

---

Contact Shrewd'm
Contact the developer of these message boards.