Formula to use different array based on selected month

[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?

 

[jim may]

A Screenshot might help us better understand; Can you provide?

 

[BigDelGooner]

Thank very much for getting back to me but I recently solved the issue.

Many thanks.

 

[jim may]

Sharing your solution helps others; Can you do so?

 

[BigDelGooner]

Of course....

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