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.

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Moving Average	2016-10	2016-11	2016-12	2017-01	2017-02	2017-03	2017-04	2017-05	2017-06	2017-07	2017-08	2017-09
2		65,754	45,075	32,643	75,236	64,357	86,467	87,235	32,763	54,752	34,452

<tbody>
</tbody>

 

[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))))