Average of the 5 best numbers out of 6 in a range?

[obiwann]

Can someone help with a formula to find out the Average of the 5 best numbers out of 6 in a range?
I know you need to find the farthest number from the mean of the 6.
A1 = 1.00105
A2 = 1.00116
A3 = 1.00177
A4 = 1.00096
A5 = 1.00108
A6 = 1.00098

 

[obiwann]

This will do it,

2 formula choices for new average
You don't need D2 (farthest) if you don't want it.

Book3.xlsx
	A	B	C	D	E
1	1.00105		Result>>	# farthest	average best 5
2	1.00116			1.00177	1.001046
3	1.00177				1.001046
4	1.00096
5	1.00108
6	1.00098
Sheet945

Cell Formulas
Range	Formula
D2	D2	=IF(MAX(A1:A6)-AVERAGE(A1:A6)>AVERAGE(A1:A6)-MIN(A1:A6),MAX(A1:A6),MIN(A1:A6))
E2	E2	=AVERAGEIF(A1:A6,"<>"&IF(MAX(A1:A6)-AVERAGE(A1:A6)>AVERAGE(A1:A6)-MIN(A1:A6),MAX(A1:A6),MIN(A1:A6)),A1:A6)
E3	E3	=(SUM(A1:A6)-IF(MAX(A1:A6)-AVERAGE(A1:A6)>AVERAGE(A1:A6)-MIN(A1:A6),MAX(A1:A6),MIN(A1:A6)))/5

That works great! One more question, Instead of the final step of finding the average of the best 5 numbers how would I incorporate into you formula finding the repeatability of the 5 best numbers.

 

[jtakw]

That works great! One more question, Instead of the final step of finding the average of the best 5 numbers how would I incorporate into you formula finding the repeatability of the 5 best numbers.

I don't understand what you mean, can you explain clearly.

 

[obiwann]

I don't understand what you mean, can you explain clearly.

Here is a formula if were determining the repeatability on the six numbers.
{=(MAX(A1:A6)/MIN(A1:A6))-1}
{=0.0167}

 

[Eric W]

This should give you the "repeatability" value for the 5 best values:

Book1
	A	B	C	D	E	F
1	1.00105		Result>>	# farthest	average best 5	Repeatability
2	1.00116			1.00177	1.001046
3	1.00177				1.001046	0.000199808
4	1.00096
5	1.00108
6	1.00098
Sheet2

Cell Formulas
Range	Formula
D2	D2	=IF(MAX(A1:A6)-AVERAGE(A1:A6)>AVERAGE(A1:A6)-MIN(A1:A6),MAX(A1:A6),MIN(A1:A6))
E2	E2	=AVERAGEIF(A1:A6,"<>"&IF(MAX(A1:A6)-AVERAGE(A1:A6)>AVERAGE(A1:A6)-MIN(A1:A6),MAX(A1:A6),MIN(A1:A6)),A1:A6)
E3	E3	=SUM(A1:A6,-D2)/(COUNT(A1:A6)-1)
F3	F3	=PRODUCT(AGGREGATE({14,15},6,A1:A6/(A1:A6<>D2),1)^{1,-1})-1

Also, I got .000809 using your formula for repeatability for all 6 values, not .0167. I'm not sure why we're different.

 

[obiwann]

=PRODUCT(AGGREGATE({14,15},6,A1:A6/(A1:A6<>D2),1)^{1,-1})-1

What is the {14,15}?

 

[Eric W]

The formula starts by making an array of the 6 numbers (A1:A6). It then divides by (A1:A6<>D2), meaning that the outlier value becomes a #DIV/0! error. The 6 parameter in AGGREGATE means to ignore errors, so the outlier is ignored. The 14 parameter means LARGE, and the 15 parameter means SMALL. So in combination with the final 1 parameter, AGGREGATE returns a 2-element array, the first element being the largest value from the array, and the second element being the smallest value. Then we raise the two values to the power of {1,-1} respectively, giving the original largest number, and the reciprocal of the smallest number, then PRODUCT multiplies those together, and finally we subtract 1 from the result. This should be equivalent to the formula you showed in post 13.

 

[obiwann]

This should give you the "repeatability" value for the 5 best values:

Book1
	A	B	C	D	E	F
1	1.00105		Result>>	# farthest	average best 5	Repeatability
2	1.00116			1.00177	1.001046
3	1.00177				1.001046	0.000199808
4	1.00096
5	1.00108
6	1.00098
Sheet2

Cell Formulas
Range	Formula
D2	D2	=IF(MAX(A1:A6)-AVERAGE(A1:A6)>AVERAGE(A1:A6)-MIN(A1:A6),MAX(A1:A6),MIN(A1:A6))
E2	E2	=AVERAGEIF(A1:A6,"<>"&IF(MAX(A1:A6)-AVERAGE(A1:A6)>AVERAGE(A1:A6)-MIN(A1:A6),MAX(A1:A6),MIN(A1:A6)),A1:A6)
E3	E3	=SUM(A1:A6,-D2)/(COUNT(A1:A6)-1)
F3	F3	=PRODUCT(AGGREGATE({14,15},6,A1:A6/(A1:A6<>D2),1)^{1,-1})-1

Also, I got .000809 using your formula for repeatability for all 6 values, not .0167. I'm not sure why we're different.

How would I nest this into one formula?

 

[Eric W]

If you really wanted, you could just put the D2 formula in the F2 formula. But after thinking about it a bit more, this should work and is shorter:

=MIN(LARGE(A1:A6,{1,2})/SMALL(A1:A6,{2,1}))-1

 

[obiwann]

If you really wanted, you could just put the D2 formula in the F2 formula. But after thinking about it a bit more, this should work and is shorter:

=MIN(LARGE(A1:A6,{1,2})/SMALL(A1:A6,{2,1}))-1

Wow! That works great. Thank you everyone!