Annualized rolling (moving) average
Thanks, I got it to work this time and I understand it better than the
sumproduct function. I don't know what happened the first time.
Thanks again!
"Bob Phillips" wrote:
=AVERAGE(IF(($A$1:$A3<$A3)*($A$1:$A3=$A3-365),$B$1:$B3))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Hunter" wrote in message
...
I am using the following formula to calculate an average of data for a
rolling 365 day period. Column A is the date, column B is Data
=((SUMPRODUCT(--($A$1:$A3<$A3),--($A$1:$A3=$A3-365),($B$1:$B3))))/(SUMPRODU
CT(--($A$1:$A3<$A3)*--($A$1:$A3=$A3-365)))
Is there a simpler way to accomplish this?
How can I calculate the standard deviation for the same period?
|