30 day Rolling Average

[mrzoogle]

Hello awesome people,

I have been trying to get this 30 day rolling average formulae working for few days now but couldn't so I was hoping if you guys could help me to the right direction please.

I have also searched chandoo forum for this formula but couldn't find one which meets my criteria.

Basically I would like to find out the average spend for 30 days for all the dates in column "Date".

My current formulae:

=AVERAGEIFS(C$3:C$1048575,$B$3:$B$1048575,">="&$B3-29,$B$3:$B$1048575,"<="&$B3)

does calculate the rolling average for 30 days but don't know how to handle the blank cells therefore resulting wrong output.

Date Total Spend Rolling Average 30 day
18/02/2013 128 128
19/02/2013 131 130
20/02/2013 78 112
21/02/2013 112
22/02/2013 70 102

Also I am not sure if I am approaching this issue in a correct way.

Thanks for your time.

Kind Regards,
Z.

 

[Luke M]

We need to account for the difference in # of rows vs. # of cells with numbers. Put this in D3 and copy down.
=AVERAGEIF(B$3:B3,">="&B3-29-(ROWS(B$3:B3)-COUNT(C$3:C3)),C$3:C3)

 

[NARAYANK991]

Hi Luke ,

I think there may be a problem in the posted formula ; try this , entered as an array formula , using CTRL SHIFT ENTER :

=AVERAGEIF($B$3:$B3,">="&INDEX($B$3:$B3,MATCH(MAX(1,COUNT($C$3:$C3)-30+1),LOOKUP(COUNTIF(OFFSET($C$3,,,ROW($C$3:$C3)-2),"<>"),ROW($C$3:$C3)-2),0)),$C$3:$C3)

The highlighted portion gives the threshold date beyond which we will get 30 days of values.

To test , choose row 69 , which has a date value of April 25, 2013 ( 41389 ) ; placing the cursor here , and going back to get a count of 30 values in column C , we arrive at row 35 , which has a date value of March 22, 2013 ( 41355 ).

The portion B3-29-(ROWS(B$3:B3)-COUNT(C$3:C3) will give 41389 - 29 - 67 + 56 = 41349. Logically , the count of actual values starting from the beginning will not necessarily give us a correct value of where the 30th value counting backwards is , since all the blanks may be bunched together. We always need to start counting backwards.

Narayan

 

[Somendra Misra]

Hi,

Considering Spend value in C3:C81 and dates in B3:B81, try using this formula.

=AVERAGE(OFFSET(C3,0,0,IF(ROWS(F$3:F3)=1,1,-MIN(30,COUNT($B$3:B3))),1))

This is not an array formula, so just enter with Enter & formula is in column F3 and down.

Regards,

Sorry, I did not read your post properly, in my formula it is considering last 30 values whether blank or not.
One question. You want to include or exculde blank data cells from calculation?

 

[mrzoogle]

Thank you all for your help, I will try all the suggested formulae and will sure have few more questions so that I can understand the formula better

@Somendra Misra, I would like to exclude blank data cells please, thanks. So for example if we have 2 blank cells in last 30 values the formulae should calculate data for only 28 days.

Thanks you all again, will get back to you soon

 

[NARAYANK991]

Hi ,

A lot of confusion over nothing !

A 30-day rolling average is an average taken over the last 30 days ; this can be done using a very simple formula , since all it involves is just going back 30 days every time.

The complexity comes in only when you say that blank cells are to be excluded , since in this case going back 30 days each time may not bring back 30 days of data ; in some cases , it may be less. If we really want the average to always include 30 days of data , we need to go back more than 30 days.

An average formula using the Excel AVERAGE function always excludes blank cells ; you do not need to do anything. It is only if there are zeros , that the AVERAGE function will include the zero values in its count , and hence may return the wrong average value , depending on how you are calculating the average.

So your last post is just adding to the confusion :

if we have 2 blank cells in last 30 values the formulae should calculate data for only 28 days.

This is what the AVERAGE function will do. If you include a range of 30 cells in the AVERAGE function , but 2 of those cells are blank , the average returned will be the average of 28 values.

Here is the quote from the much maligned Excel help :

If a range or cell reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

Narayan