User definable cel range for calculations?

vanclute

Board Regular
Joined
Oct 23, 2002
Messages
218
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(J6:(J6+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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

vanclute

Board Regular
Joined
Oct 23, 2002
Messages
218
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
 

Forum statistics

Threads
1,144,363
Messages
5,723,917
Members
422,527
Latest member
JayTheKaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top