I want to build a donchian breakout-type functionality into a strategy I built in Excel 2003 (i.e. highest high of x days...). The easy way to calculate it is to type =MAX(A1:A20), where column A is the highs of each bar. However, I would like to vary the look-back period by typing in a number (5, 15, 22, etc.) and have it automatically adjust the cells to put in the calculation. Is there a way to do this in Excel, without coding in VB? I can't even code "hello world" in VB. (Note: I don't trade off of excel...I just backtest. Lots of nested IF statements!)
I don't know Excel so this is just an untested guess. Assumptions: - you have your highs in column A with the most recent bar high in A1 and subsequent bar highs in A2,A3...AX - you are specifying the integer lookback period in cell B1 Formula: =MAX(A1:OFFSET(A1,B1,0)) NOTE: When you enter 1 into cell B1, assuming it works, the formula will actually look at the MAX of both A1 and A2 i.e. 2 periods. So, you may want/have to subtract one in the formula e.g. =MAX(A1:OFFSET(A1,B1-1,0))
byteme, that is a very cool formula. I've used Excel in my work everyday for about 15 yrs and have never come across this function and it's usage. Very cool formula. I attached a sample. Thanks for sharing the formula.