Excel formula to calculate average value from first non-zero in a range, to last non-zero in a range

[pcgamer]

Good evening.

Could you please assist me in calculating the average between a range of values in Microsoft Excel 2016 (or Office 365).
I wish to find the average between the first non-zero and the last non-zero of each row.

If I could break this up into 3 steps in the formula to calculate the resulting average number.

1st - To find the cell reference of the first non-zero from a row of data (or a range)
2nd - To find the cell reference of the last non-zero in the same row of data (or a range).
3rd - To find the average between those two cell references
​

Row 1 Table Example
In the below table example, there are two rows of data shown. For Row 1,

the first non zero that occurs in Row 1, is in Column C (the value is 14). Thus the first non zero in the range $A1:$F1 is C1.
​

The last non zero that occurs in Row 1, is Column E (the value is 12). Thus the last non zero in the same range $A1:$F1 is E1.
The average of these two references would thus be 12. That is the average of the numbers between C1 and E1, divided by the count of the numbers. Zero values are included.
​

I would like to copy the final formula down for each row, Row 1, Row 2, ..... Row 100 etc

Example Data

[TABLE="class: grid, width: 500, align: left"]
<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"]First non-Zero Reference[/TD]
[TD="align: center"]Last non-Zero Reference[/TD]
[TD="align: center"]Result
(Average)
[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]14
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]12
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]E1[/TD]
[TD="align: center"]12
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]21
[/TD]
[TD="align: center"]22
[/TD]
[TD="align: center"]23
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]24
[/TD]
[TD="align: center"]A2[/TD]
[TD="align: center"]F2[/TD]
[TD="align: center"]15
[/TD]
[/TR]
</tbody>[/TABLE]


Thank you so much for your assistance in advance.

Kylie

 

[mole999]

if F2 is really the last non zero then the result is 22.5 (with =AVERAGEIF(A1:F1,"<>0"))

 

[kvsrinivasamurthy]

ARRAY formula for Row 1

=SUM(B4:G4)/((SMALL(IF(SUMIF(OFFSET(B4,0,0,1,COLUMN(B4:G4)-COLUMN(B4)+1),">=0")=SUM(B4:G4),COLUMN(B4:G4),""),1)-COLUMN(B4)+1)-IFERROR(MATCH(0.0001,B4:G4,1),0))

then drag down.

ARRAY formula is used


To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.

 

[Aladin Akyurek]

@pacgamer

Those 12 and 15 in the I-range really need an explanation: Can you spell how do they obtain manually?

 

[NARAYANK991]

Hi ,

Would this work ?

=AVERAGE(INDEX($A2:$F2, MATCH(1, ($A2:$F2 <> 0) * 1, 0)):INDEX($A2:$F2,LOOKUP(2,1/($A2:$F2 <> 0), COLUMN($A2:$F2) - MIN(COLUMN($A2:$F2)) + 1)))

This is an array formula , to be entered using CTRL SHIFT ENTER.

 

[shift-del]

And another one...

=AVERAGE(INDEX(A1:F1,AGGREGATE(15,6,COLUMN(A1:F1)/(A1:F1<>0),1)):INDEX(A1:F1,AGGREGATE(14,6,COLUMN(A1:F1)/(A1:F1<>0),1)))

 

[Nishant94]

Unknown
	A	B	C	D	E	F	G
1	A	B	C	D	E	F	Result (Average)
2	0	0	14	10	12	0	12
3	21	22	23	0	0	24	15
Sheet8

Cell Formulas
Range	Formula
G2	=AVERAGE(INDEX(A2:F2,AGGREGATE(15,6,(COLUMN(A2:F2)-COLUMN(A2)+1)/(A2:F2<>0),1)):INDEX(A2:F2,AGGREGATE(14,6,(COLUMN(A2:F2)-COLUMN(A2)+1)/(A2:F2<>0),1)))

 

[pcgamer]

Hi Nishant94. This formula you have created works perfectly. I cant thank you enough. I have been pulling my hair out for weeks trying to determine if it can be done. Again, its spot on!