Interactive formulas
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. |
Interactive formulas
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. |
Interactive formulas
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. |
Interactive formulas
"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) |
All times are GMT +1. The time now is 02:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com