General Topics
Markets
Technical Topics
Brokerage Firms
Community Lounge
Site Support

# 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.

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))

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.

File size:
15 KB
Views:
115
4. ### drm7

Thanks guys, this is perfect.

ET IS FREE BECAUSE OF THE FINANCIAL SUPPORT FROM THESE COMPANIES: