Saturday, November 2, 2013

yogi_Compute Row By Row Moving Average (or Sum) Of Data In Column A For Specified Number Of Items

                                          Google Spreadsheet   Post  #1408
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 02, 2013
question by mep2hu (http://productforums.google.com/forum/?zx=pmz6oo65xaw#!mydiscussions/docs/mgfGOYDzAQw)
Array Formula Moving Average
Consider this spreadsheet:
DateValueAverage 10 day
1/6/201378
1/7/201371
1/8/201367
1/9/201364
1/10/201362
1/11/201360
1/12/201360
1/13/201365
1/14/201365
1/15/201366
1/16/201365
1/17/201363
1/18/201363
1/19/201362
1/20/201361
1/21/201362
1/22/201360
1/23/201358
1/24/201359
1/25/201360
1/26/201361
1/27/201360
1/28/201360
1/29/201359


Is there an arrayformula that I can put in the shaded box (C11) that will calculate a 10-day moving average for the values in column B.

Average(B2:B9) can be copied down.   Arrayformula(Average(B2:B : B9:B) doesn't seem to work.

Thanks.
---
hanks for the answers guys.   Yogi, I have included a link - perhaps it will help.

APL, I tried your formula and it gave an average for 11 rather than 10 - so I tried to adapt it for 10, and it works for all values but the first.

I use this a lot, so any additional help would be appreciated.



-------------------------------------------------------------------------------------------------------------------------------------