/docs/community?hl=en
/docs/community?hl=en
10/7/13
Original Poster
meph2u

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.
Community content may not be verified or up-to-date. Learn more.
All Replies (7)
legine
10/8/13
legine
The simplest solution I com up with is writing the Formula yourself.
For a simple moveing Average that would be
AVerage 10 days = C2!

Formula for C2= B2
Formula for C3 = (C2+B2)/(Row(B2)-1)

You need the -1 because you have a Headerline in your Spreadsheet.

I hope that is what you want. Wikipedia would be a nice start for more complex moveing Average, or some Math book from store ;) The Principle schould be similar.
Yogi Anand
10/8/13
Yogi Anand
Hi meph2u:

Let me add to contribution from legine ...

just to be clear how many numbers you want moving average for,
please show a few of your expected results in cells of column C
along with needed logic explanation as to why those are correct results 

and then let us take it from there.


Make It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
AD:AM
10/8/13
AD:AM
Try in C11:

=ArrayFormula(IF(B11:B;MMULT((ROW(B11:B)>=TRANSPOSE(ROW(B2:B)))*((ROW(B11:B)-9)<=TRANSPOSE(ROW(B2:B)));B2:B)/10;IFERROR(1/0)))
11/2/13
Original Poster
meph2u
Thanks 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.



11/2/13
Original Poster
meph2u
I did figure out a "brute force" formula (duh).   But it involves typing as many cells as you want in the moving average.  

=arrayformula((A2:A+A3:A+A4:A+A5:A+A6:A+A7:A+A8:A+A9:A+A10:A+A11:A)/10)

Though in the example, I used moving average of 10 items, I often do 21, 40, 60, 90, so this solution would be quite cumbersome in reality.  I added it to the spreadsheet.  Along with a 21 column to assist in generalizing.
Yogi Anand
11/2/13
Yogi Anand
Hi mep2hu:

In addition to excellent contribution from Top Contributor APL, I have built up a solution to a bit more generalized problem as presented in my following blog post

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

so please check it out to see how this works for you.


Make It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com


11/2/13
Original Poster
meph2u
Thanks Yogi....it works, for sure!   I am having real difficulty unpacking it...but I can use it.

So, how would you change it if you wanted a moving sum rather than a moving average?  :-)
Were these replies helpful?
How can we improve them?
 
This question is locked and replying has been disabled. Still have questions? Ask the Help Community.

Badges

Some community members might have badges that indicate their identity or level of participation in a community.

 
Expert - Google Employee — Googler guides and community managers
 
Expert - Community Specialist — Google partners who share their expertise
 
Expert - Gold — Trusted members who are knowledgeable and active contributors
 
Expert - Platinum — Seasoned members who contribute beyond providing help through mentoring, creating content, and more
 
Expert - Alumni — Past members who are no longer active, but were previously recognized for their helpfulness
 
Expert - Silver — New members who are developing their product knowledge
Community content may not be verified or up-to-date. Learn more.

Levels

Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

  • Post an answer.
  • Having your answer selected as the best answer.
  • Having your post rated as helpful.
  • Vote up a post.
  • Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

View profile in forum?

To view this member's profile, you need to leave the current Help page.

Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.