Sunday, March 10, 2013

yogi_Compute 3 Month (current month and prior two months) Rolling Average Of Specified Metrics


                                          Google Spreadsheet   Post  #1078
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 10, 2013
user David LaLande :(http://productforums.google.com/forum/?zx=wp0u4iwq5gs6#!category-topic/docs/spreadsheets/pN8t5vketRI)

I need some help with a formula for a 3 month rolling average.  I've tried it a couple different ways but I can't get it right.  Any help would be appreciated.

Here is my sheet.


Thank you,

Dave
---
I have values in the first three rows of column C starting at C2.  I am trying to do a rolling 3 month average of column C.  I'm not getting it done.

=average(offset(C2:C14;if(row(C2)-row($C$1)<2;row($C$1)-row(C2)+1;-3);0;if(row(C2)-row($C$1)<3;row(C2)-row($C$1);3);3))
----------------------------------------------------------------------------------------------------------------
following is a solution to the problem