I am calculating a rolling historical volatility for a series of commodity
returns, using the following formula =STDEV(D6:D35)*SQRT(252)
I want to be able to type in the # of days back to go (e.g. in the example
above, it is a 30-day rolling stdev) in the cell above the top value. That
is, I want to be able to type "50" into the cell and have the formula update
to STDEV(D6:D55)*SQRT(252) automatically.

Assuming you enter the number of days in A1, try this:

=IF(A10,STDEV(INDIRECT("D6:D"&A1+5)*SQRT(252),0)

This returns 0 if A1 is blank - you might also want to set some
maximum for A1, such as:

=IF(AND(A10,A1<100),STDEV(INDIRECT("D6:D"&A1+5)*S QRT(252),0)

Hope this helps.

Pete

On Aug 14, 12:20 am, energydoc
wrote:
I am calculating a rolling historical volatility for a series of commodity
returns, using the following formula =STDEV(D6:D35)*SQRT(252)
I want to be able to type in the # of days back to go (e.g. in the example
above, it is a 30-day rolling stdev) in the cell above the top value. That
is, I want to be able to type "50" into the cell and have the formula update
to STDEV(D6:D55)*SQRT(252) automatically.

Hi

=STDEV(OFFSET($D$6,,,$X$1,1)*SQRT(252)

, where cell X1 contains the number of days


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"energydoc" wrote in message
...
I am calculating a rolling historical volatility for a series of commodity
returns, using the following formula =STDEV(D6:D35)*SQRT(252)
I want to be able to type in the # of days back to go (e.g. in the example
above, it is a 30-day rolling stdev) in the cell above the top value.
That
is, I want to be able to type "50" into the cell and have the formula
update
to STDEV(D6:D55)*SQRT(252) automatically.

"Arvi Laanemets" wrote...
=STDEV(OFFSET($D$6,,,$X$1,1)*SQRT(252)
....

OFFSET is a volatile function, so it recalcs all the time. One nonvolatile
alternative would be

=STDEV($D$6:INDEX($D:$D,$X$1+5))*SQRT(252)