Use index function to shift range in a patterned formula

**Ron Coderre** · 07-05-2011, 01:05 PM

I *think* I understand what you're hoping to achieve.

Using your posted workbook:
This regular formula increments the referenced range by 2 cells for each 1 cell that you copy the formula down Col_N, always referencing 60 cells.

 N72: =STDEV(INDEX(_RR,ROWS(N$72:N72)*2):INDEX(_RR,ROWS(N$72:N72)+59))*SQRT(12)

Copy that formula down as far as you need.

Is that something you can work with?

**ndube50** · 07-06-2011, 04:29 PM

Originally Posted by Ron Coderre

I *think* I understand what you're hoping to achieve.

Using your posted workbook:
This regular formula increments the referenced range by 2 cells for each 1 cell that you copy the formula down Col_N, always referencing 60 cells.

 N72: =STDEV(INDEX(_RR,ROWS(N$72:N72)*2):INDEX(_RR,ROWS(N$72:N72)+59))*SQRT(12)

Copy that formula down as far as you need.

Is that something you can work with?

Hi Ron,

To be honest, I'm still having some difficulties with it.
Perhaps my dynamic named range (_RR) is referencing the wrong cell?
The value in cell N72 should equal the value in K72 (volatility). I then want to be able to drag the cell and have it automatically populate the cells correctly using the correct formula which increments the referenced range by 2 cells for each 1 that I copy down.

Does that make sense?

Thanks again for your help with this one.

nick.