Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving Weighted Average formula | Excel Discussion (Misc queries) | |||
How to create a rolling 6 month average? | Excel Discussion (Misc queries) | |||
rolling average | Excel Worksheet Functions | |||
Moving Average projection? | Excel Worksheet Functions | |||
30 Day Moving Average Ignoring Blank Cells | Excel Worksheet Functions |