Change range in formula dynamically PLEASE HELP!!!!

[BigDelGooner]

Please help someone!!

I have a sheet where I would like to calculate the 12m standard deviation of a given set of figures based on the month selected (i.e. 12 months to 30/09/09 or 31/10/09).

I know the basic formula for what I am trying to do is =STDEV(Data!X63:X74) for the 12 months to 30/09/09 but what can I add to make this formula dynamically change the range to =STDEV(Data!X64:X75) for the 12 months to 31/10/09.

All of the data is in a named range called 'Data_Range' with the EOMONTHs in the first column which I was hoping might work as a reference for this formula.

Any ideas guys?

 

[Andrew Poulsom]

If the date is in A1 and the data is in column 2 of the range:

=STDEV(OFFSET(Data_Range,MATCH(A1,INDEX(Data_Range,,1))-12,1,12,1))

or using the non-volatile INDEX:

=STDEV(INDEX(Data_Range,MATCH(A1,INDEX(Data_Range,,1))-11,2):INDEX(Data_Range,MATCH(A1,INDEX(Data_Range,,1)),2))

 

[BigDelGooner]

Sorry to be a real pain but I am being incredibly dense, the data I need is in column 7 but I cant see where I would amend your formulas to reflect this...

 

[BigDelGooner]

Please ignore me i was being extremely stupid!

Many thanks for your help andrew, as always!