Moving Average Column Last 3 Values

[lynette1978]

I'm trying to create a moving average so when I enter the values for each month, it averages the last 3 values in that row, even if the value for that month is zero (not blank). I'm not sure how to do this. The 3-Mo average would go in cell A2 and I would update the values in cells B2 through M2 for each month.

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[TD="align: center"]M
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Moving Average
[/TD]
[TD]2016-10
[/TD]
[TD]2016-11
[/TD]
[TD]2016-12
[/TD]
[TD]2017-01
[/TD]
[TD]2017-02
[/TD]
[TD]2017-03
[/TD]
[TD]2017-04
[/TD]
[TD]2017-05
[/TD]
[TD]2017-06
[/TD]
[TD]2017-07
[/TD]
[TD]2017-08
[/TD]
[TD]2017-09
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]65,754
[/TD]
[TD]45,075
[/TD]
[TD]32,643
[/TD]
[TD]75,236
[/TD]
[TD]64,357
[/TD]
[TD]86,467
[/TD]
[TD]87,235
[/TD]
[TD]32,763
[/TD]
[TD]54,752
[/TD]
[TD]34,452
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[footoo]

One possibility:
=IF(COUNTA(B2:M2)<3,"",(OFFSET(A2,0,COUNTA(B2:M2)-2) +OFFSET(A2,0,COUNTA(B2:M2)-1) +OFFSET(A2,0,COUNTA(B2:M2)))/3)

(There must surely be a more concise way)

 

[lynette1978]

Worked beautifully. Thanks!

 

[Aladin Akyurek]

Try...

=AVERAGE(OFFSET(INDEX(B2:M2,MATCH(9.99999999999999E+307,B2:M2)),0,0,1,-MIN(3,COUNT(B2:M2))))