Really basic excel question

D

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!)
 
Quote from 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!)
More...

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.
 

Attachments

You must log in or register to reply here.
Back
Top