Calculate Moving Average in Excel

Question

I want to calculate a moving average of the last, say 20, numbers of a column. A problem is that some of the cells of the column may be empty, they should be ignored. Example:

 A
175
154

188
145
155

167
201

A moving average of the last three would be (155+167+201)/3. I've tried to implement this using average, offset, index, but I simply don't know how. I'm a little bit familiar with macros, so such a solution would work fine: =MovingAverage(A1;3)

Thanks for any tips or solutions!

Answer 1

{=SUM(($A$1:A9)*(ROW($A$1:A9)>LARGE((ROW($A$1:A9))*(NOT(ISBLANK($A$1:A9))),3+1)))/3}

Enter this with control+shift+enter to make it an array formula. This will find the latest three values. If you want more or less, change the two instances of '3' in the formula to whatever you want.

LARGE((ROW($A$1:A9))*(NOT(ISBLANK($A$1:A9))),3+1)

This part returns the 4th highest row number of all the cells that have a value, or 5 in your example because rows 6, 8, and 9 are the 1st through 3rd highest rows with a value.

(ROW($A$1:A9)>LARGE((ROW($A$1:A9))*(NOT(ISBLANK($A$1:A9))),3+1))

This part returns 9 TRUEs or FALSEs based on whether the row number is larger than the 4th largest.

($A$1:A9)*(ROW($A$1:A9)>LARGE((ROW($A$1:A9))*(NOT(ISBLANK($A$1:A9))),3+1))

This multiplies the values in A1:A9 by those 9 TRUEs or FALSEs. TRUEs are converted to 1 and FALSEs to zero. This leaves a SUM function like this

=SUM({0;0;0;0;0;155;0;167;201})/3

Because all the values above 155 don't satisfy the row number criterion, the get multiplied by zero.