User definable cel range for calculations?

[vanclute]

I have a calculation that I want to do on a range of cels, however I want the number of cels in the range to be variable, and defined by another cel. To be specific, I'm calculating historic volatility of stock prices, but I want to do it for whatever number of days I'm interested in. THe formula I have that works fine for a specific range is:

=STDEV(J6:J259)*SQRT(252)

Where J6:J259 is one year's worth of valatility data.

I want to do something like:

=STDEV(J6J6+K1))*SQRT(252)

Where K1 is the number of days of volatility I am interested in at the moment. The above formula of course, does not work. Anyone know how to accomplish this? I have used various methods to be able to pull specific day's data from the list, but I need to calculate on a RANGE, not just a specific date.

Thanks in advance!

Jonathan
vanclute@fastmail.fm

 

[Barrie Davidson]

Jonathan, try this formula:

=STDEV(OFFSET(J6,0,0,K1))*SQRT(252)

Regards,

 

[vanclute]

THANK YOU!!

This worked, with one slight catch that took me a bit to realize. The formula you wrote:

=STDEV(OFFSET(J1,0,0,K1))*SQRT(252)

Needed one change, which in hindsight was obvious. It needed to refer to a RANGE as its starting point, not just a singular value.

The final formula is:

=STDEV(OFFSET(O9:O259,0,0,T6))*SQRT(252)

And it does exactly what I wanted! Thank you so very much!

Jonathan