Really basic excel question

Discussion in 'Automated Trading' started by drm7, Dec 20, 2010.

  1. drm7


    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!)
  2. byteme


    I don't know Excel so this is just an untested guess.


    - 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



    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.

  3. smili


    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.
  4. drm7


    Thanks guys, this is perfect.