# User definable cel range for calculations?

#### vanclute

##### Board Regular
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.

Jonathan
vanclute@fastmail.fm

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### Barrie Davidson

##### MrExcel MVP
Jonathan, try this formula:

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

Regards,

#### vanclute

##### Board Regular
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

Replies
2
Views
186
Replies
5
Views
395
Replies
12
Views
849
Replies
3
Views
638
Replies
3
Views
918

1,181,338
Messages
5,929,389
Members
436,665
Latest member
MonkeyNotDonkey

### 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.

### Which adblocker are you using?

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

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