Annualized rolling (moving) average
 

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))))/(SUMPRODUCT(--($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?

Annualized rolling (moving) average
 

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

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?