Moving average ignoring 0

[Super_ryan94]

Hey all,

I could not find an answer that would suit my needs looking on the internet, so hope you can help me on this one.

I have a big table that will have a length of 28800 cells or more with the end containing zeros.
I want to have a moving average of the last 1800 cells not containing any zeros.
If there are less then 1800 cells not containing any zeros, just take the average over the cells within the rules.

The closest formula to suit my needs needed me to type out every cell which I for obvious reasons did not want to do.

What would be the best formula to use for this?

Sincerely, Ryan

 

[Crystalyzer]

I think this would do the trick if you are referring to cells being in rows.

=IF(NOT(ISERROR(SUM(INDIRECT("A"&MAX(2,ROW(A2)-4)&":A"&ROW(A2)))/(COUNTIF(INDIRECT("A"&MAX(2,ROW(A2)-4)&":A"&ROW(A2)),">"&0)))),SUM(INDIRECT("A"&MAX(2,ROW(A2)-4)&":A"&ROW(A2)))/(COUNTIF(INDIRECT("A"&MAX(2,ROW(A2)-4)&":A"&ROW(A2)),">"&0)),0)

Book3
	A	B
1	Values	Trailing 4 row Avg
2	2	2
3	3	2.5
4	4	3
5	0	3
6	0	3
7	2	3
8	1	2.333333333
9	5	2.666666667
10	4	3
11	3	3
12	2	3
13	0	3.5
14	0	3
15	0	2.5
16	1	1.5
17	0	1
18	7	4
19	6	4.666666667
20	5	4.75
21	0	6
22	0	6
23	0	5.5
24	0	5
25	0	0
26	0	0
Super_ryan94

Cell Formulas
Range	Formula
B2:B26	B2	=IF(NOT(ISERROR(SUM(INDIRECT("A"&MAX(2,ROW(A2)-4)&":A"&ROW(A2)))/(COUNTIF(INDIRECT("A"&MAX(2,ROW(A2)-4)&":A"&ROW(A2)),">"&0)))),SUM(INDIRECT("A"&MAX(2,ROW(A2)-4)&":A"&ROW(A2)))/(COUNTIF(INDIRECT("A"&MAX(2,ROW(A2)-4)&":A"&ROW(A2)),">"&0)),0)

 

[Super_ryan94]

Thanks for the quick reply.

That is not exactly what I was looking for.
I thinking of having all the calculation happen in 1 cell under a sign with average.

Since I have almost 200 columns having the calculation being done in just a since cell would make it easier for me to see everything.

Sincerely, Ryan

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Super_ryan94]

Ah, my bad.

I'm using excel16 on windows.

 

[Crystalyzer]

Do you want averages across columns or down rows?

 

[Fluff]

Thanks for that, how about

=AVERAGE(INDEX(A2:A30000,AGGREGATE(14,6,(ROW(A2:A30000)-ROW(A2)+1)/(A2:A30000<>0),ROW(INDIRECT("1:"&MIN(1800,COUNTIF(A:A,">0")))))))

 

[Super_ryan94]

Book1
	A	B	C	D	E	F	G	H
1	A	B	C		avg A	avg B	avg C	avg Over
2	1	1	1		cells 1-6	cells 2-7	cells 1-4	6
3	2	2	2
4	3	3	3
5	4	4	4
6	5	5	0
7	6	6	0
8	0	7	0
9	0	0	0
10	0	0	0
Sheet1

Found the add-in \o/

I'm looking for something like this.

 

[Super_ryan94]

Thanks for that, how about

=AVERAGE(INDEX(A2:A30000,AGGREGATE(14,6,(ROW(A2:A30000)-ROW(A2)+1)/(A2:A30000<>0),ROW(INDIRECT("1:"&MIN(1800,COUNTIF(A:A,">0")))))))

That does the trick wonderfully easy XD
Thanks you, you solved my problem

 

[Fluff]

Glad we could help & thanks for the feedback.