Moving average last 5 numeric number, N/A for any zero cells and the omitting that for next moving average

[ttngu17]

Hello,
I have been trying to find the formula that will work for the finding the moving average of the last 5 numeric number , for those that have "0" value would be "N/A" and that "0" would be excluded from calculating the next moving average.
Below is the table that i'm working on. I'm able to get to the following formula where it calculate moving 5 correctly, but after there is a zero entry it includes the 0 in its calculation where I would like to skip it . For example on row 11 I would like it to calculate the average of row 11,9,8,7,6

Current formula I have is
=IF(A6<=0,NA(),AVERAGE(OFFSET(A6,0,0,-5,1)))

Any help would be much appreciated. Thanks so much in advance.

1​	Data	5 days moving average
2​	1158150.93​
3​	1371140.64​
4​	944049​
5​	1143291.87​
6​	1657468.86​	1254820​
7​	1433896.28​	1309969​
8​	1634534.82​	1362648​
9​	2160770.04​	1605992​
10​	0​	#N/A​
11​	1903487.21​	1426538​
12​	1709663.66​
13​	1394217.07​
14​	1253709.87​
15​	1183419.02​

 

[Cubist]

Try:

Book2
	A	B
1	Data	5 days moving average
2	1158150.93	1158150.93
3	1371140.64	1264645.785
4	944049	1157780.19
5	1143291.87	1154158.11
6	1657468.86	1254820.26
7	1433896.28	1309969.33
8	1634534.82	1362648.166
9	2160770.04	1605992.374
10	0	#N/A
11	1903487.21	1758031.442
12	1709663.66	1768470.402
13	1394217.07	1760534.56
14	1253709.87	1684369.57
15	1183419.02	1488899.366
Sheet3

Cell Formulas
Range	Formula
B2:B15	B2	=IF(A2:A15=0,NA(),MAP(A2:A15,LAMBDA(m,AVERAGE(TAKE(FILTER(A2:m,A2:m<>0),-5)))))
Dynamic array formulas.

 

[ttngu17]

This is amazing, thank you so much!!!!!!

 

[Cubist]

You're welcome.