Interactive formulas

[energydoc]

I am calculating a rolling historical volatility for a series of commodity
returns, using the following formula =STDEV(D635)*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(D655)*SQRT(252) automatically.

 

[Pete_UK]

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

=IF(A1>0,STDEV(INDIRECT("D6"&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(A1>0,A1<100),STDEV(INDIRECT("D6"&A1+5)*SQRT(252),0)

Hope this helps.

Pete

 

[Arvi Laanemets]

Hi

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

, where cell X1 contains the number of days

 

[Harlan Grove]

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