Ignore Trailling Zeros in an Average

[MutanGFX]

Hello!

I have the following row with data:

0 0 0 1 6 4 2 0 1 0 0 3 4 5 3 2 3 1 0 0 0 0

I'm currently using the flowing formula to ignore leading zeros from the average:

=AVERAGE(INDEX($A$2:$V$2,1,MATCH(TRUE,INDEX($A$2:$V$2<>0,),0)):$V$2)

But I also need to ignore the trailling zeros, but I have no idead how to perform the same trick from right to left to find the first cell <>0

Any help will be much appreciated.

 

[JamesCanale]

MrExcelPlayground11.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	0	0	0	1	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	0	0	0
2	2.3333
3
4	0	0	0	4	5	6	7	0	9	0	0	12	13	14	15	16	17	18	0	0	0	0
5	4	5	6	7	9	12	13	14	15	16	17	18
6	4
7	18
8	1	6	4	2	0	1	0	0	3	4	5	3	2	3	1
9	2.3333
Sheet17

Cell Formulas
Range	Formula
A2	A2	=LET(r,A1:V1,a,(r>0)*SEQUENCE(1,COLUMNS(r)),b,FILTER(a,a>0),c,MIN(b),d,MAX(b),e,INDEX(r,1,SEQUENCE(1,d-c+1,c)),AVERAGE(e))
A4:V4	A4	=(A1:V1>0)*SEQUENCE(1,COLUMNS(A1:V1))
A5:L5	A5	=FILTER(A4#,A4#>0)
A6	A6	=MIN(A5#)
A7	A7	=MAX(A5#)
A8:O8	A8	=INDEX(A1:V1,1,SEQUENCE(1,A7-A6+1,A6))
A9	A9	=AVERAGE(A8#)
Dynamic array formulas.

 

[MutanGFX]

MrExcelPlayground11.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	0	0	0	1	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	0	0	0
2	2.3333
3
4	0	0	0	4	5	6	7	0	9	0	0	12	13	14	15	16	17	18	0	0	0	0
5	4	5	6	7	9	12	13	14	15	16	17	18
6	4
7	18
8	1	6	4	2	0	1	0	0	3	4	5	3	2	3	1
9	2.3333
Sheet17

Cell Formulas
Range	Formula
A2	A2	=LET(r,A1:V1,a,(r>0)*SEQUENCE(1,COLUMNS(r)),b,FILTER(a,a>0),c,MIN(b),d,MAX(b),e,INDEX(r,1,SEQUENCE(1,d-c+1,c)),AVERAGE(e))
A4:V4	A4	=(A1:V1>0)*SEQUENCE(1,COLUMNS(A1:V1))
A5:L5	A5	=FILTER(A4#,A4#>0)
A6	A6	=MIN(A5#)
A7	A7	=MAX(A5#)
A8:O8	A8	=INDEX(A1:V1,1,SEQUENCE(1,A7-A6+1,A6))
A9	A9	=AVERAGE(A8#)
Dynamic array formulas.

I have no idea how any of this works but thank you

 

[MutanGFX]

MrExcelPlayground11.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	0	0	0	1	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	0	0	0
2	2.3333
3
4	0	0	0	4	5	6	7	0	9	0	0	12	13	14	15	16	17	18	0	0	0	0
5	4	5	6	7	9	12	13	14	15	16	17	18
6	4
7	18
8	1	6	4	2	0	1	0	0	3	4	5	3	2	3	1
9	2.3333
Sheet17

Cell Formulas
Range	Formula
A2	A2	=LET(r,A1:V1,a,(r>0)*SEQUENCE(1,COLUMNS(r)),b,FILTER(a,a>0),c,MIN(b),d,MAX(b),e,INDEX(r,1,SEQUENCE(1,d-c+1,c)),AVERAGE(e))
A4:V4	A4	=(A1:V1>0)*SEQUENCE(1,COLUMNS(A1:V1))
A5:L5	A5	=FILTER(A4#,A4#>0)
A6	A6	=MIN(A5#)
A7	A7	=MAX(A5#)
A8:O8	A8	=INDEX(A1:V1,1,SEQUENCE(1,A7-A6+1,A6))
A9	A9	=AVERAGE(A8#)
Dynamic array formulas.

For some reason both of the above solutions are only working for the first row of data (I have multiple rows where I need to achieve the same result, average ignoring the first and last zeros)

The error is just #CALC!

Would you know how to solve that?

 

[JamesCanale]

It's just one solution - I have the compact LET formula, and then I break it out into all of the steps so you might follow what is happening (teach a man to fish...)

Here it is setup differently so you can put in multiple rows of this stuff:

MrExcelPlayground11.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
13	0	0	0	1	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	0	0	0	2.333333
14	0	0	0	0	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	0	0	0	2.428571
15	0	0	0	0	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	0	0	0	2.428571
16	0	0	5	1	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	4	0	0	2.444444
17	5	4	0	1	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	0	0	4	2.181818
18	0	12	0	1	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	5	0	0	2.736842
19	0	0	0	1	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	0	0	0	2.333333
Sheet17

Cell Formulas
Range	Formula
W13:W19	W13	=LET(r,A13:V13,a,(r>0)*SEQUENCE(1,COLUMNS(r)),b,FILTER(a,a>0),c,MIN(b),d,MAX(b),e,INDEX(r,1,SEQUENCE(1,d-c+1,c)),AVERAGE(e))

 

[MutanGFX]

It's just one solution - I have the compact LET formula, and then I break it out into all of the steps so you might follow what is happening (teach a man to fish...)

Here it is setup differently so you can put in multiple rows of this stuff:

MrExcelPlayground11.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
13	0	0	0	1	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	0	0	0	2.333333
14	0	0	0	0	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	0	0	0	2.428571
15	0	0	0	0	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	0	0	0	2.428571
16	0	0	5	1	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	4	0	0	2.444444
17	5	4	0	1	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	0	0	4	2.181818
18	0	12	0	1	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	5	0	0	2.736842
19	0	0	0	1	6	4	2	0	1	0	0	3	4	5	3	2	3	1	0	0	0	0	2.333333
Sheet17

Cell Formulas
Range	Formula
W13:W19	W13	=LET(r,A13:V13,a,(r>0)*SEQUENCE(1,COLUMNS(r)),b,FILTER(a,a>0),c,MIN(b),d,MAX(b),e,INDEX(r,1,SEQUENCE(1,d-c+1,c)),AVERAGE(e))

Thank you again!

It was my mistake, some of the rows had just 0's in them, hence the #CALC!, so I added and IFERROR(formula;0) and now everything works!