Formula to get average of 3 lowest numbers in range, whilst ignoring 0!

[chrisj2812]

Hi,

I've used =(AVERAGE(SMALL(range,{1,2,3}))) to get the average of the lowest 3 numbers in my range.

However, if there are only 1 or 2 numbers the 0's in the range are polluting the result. Is there any way to rewrite the formula to ignore them?

Many Thanks

Chris

 

[Sufiyan97]

There must be short way but for now you can Try

=(AVERAGE(SMALL($A$2:$A$10,COUNTIF($A$2:$A$10,0)+1))+AVERAGE(SMALL($A$2:$A$10,COUNTIF($A$2:$A$10,0)+2))+AVERAGE(SMALL($A$2:$A$10,COUNTIF($A$2:$A$10,0)+3)))/3

 

[Snakehips]

@chrisj2812

If you will only have zeros and negative numbers?
Maybe like..

sample.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	AW1	AW2	AW3	AW4	AW5	AW6	AW7	AW8	AW9	AW10	AW-Av
2	0	0	-85	-82	-67	0	0	0	0	0	-78
3	0	0	-71	0	0	0	0	0	0	0	-71
4	0	-68	-70	0	0	0	0	0	0	0	-69
Sheet1

Cell Formulas
Range	Formula
K2:K4	K2	=SUM(SMALL(A2:J2,{1,2,3}))/MIN(3,COUNTIF(B2:J2,"<0"))

Hope that helps.

 

[Sufiyan97]

If number are always negative

you can try

Book1
	A	B	C	D	E	F	G	H	I	J	K
2	0	0	-85	-82	-67	0	-67	0	0	0	-72
3	0	0	0	-74	-75	-69	0	0	-62	-52	-61
4	0	0	-71	0	0	0	0	0	0	0	#NUM!
Sheet2

Cell Formulas
Range	Formula
K2:K4	K2	=(AVERAGE(LARGE(A2:J2,COUNTIF(A2:J2,0)+1),LARGE(A2:J2,COUNTIF(A2:J2,0)+2),LARGE(A2:J2,COUNTIF(A2:J2,0)+3)))

 

[Sufiyan97]

If number are always negative

you can try

Book1
	A	B	C	D	E	F	G	H	I	J	K
2	0	0	-85	-82	-67	0	-67	0	0	0	-72
3	0	0	0	-74	-75	-69	0	0	-62	-52	-61
4	0	0	-71	0	0	0	0	0	0	0	#NUM!
Sheet2

Cell Formulas
Range	Formula
K2:K4	K2	=(AVERAGE(LARGE(A2:J2,COUNTIF(A2:J2,0)+1),LARGE(A2:J2,COUNTIF(A2:J2,0)+2),LARGE(A2:J2,COUNTIF(A2:J2,0)+3)))

I am sorry @chrisj2812

I did not get it right, ignore my both posts

 

[chrisj2812]

Guys, Wonderful! Thank you so much for your time and effort! It always amazes me how knowledgeable and helpful you are on this forum.

Really appreciated.