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

 

[AhoyNC]

Are the 5 best numbers the highest 5 numbers?
If so try:

Book1
	A
1	1.00105
2	1.00116
3	1.00177
4	1.00096
5	1.00108
6	1.00098
7
8	1.001208
Sheet3

Cell Formulas
Range	Formula
A8	A8	=AVERAGEIF(A1:A6,">="&LARGE(A1:A6,5))

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: find out the average of the 5 best numbers out of 6 in a range
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[obiwann]

Are the 5 best numbers the highest 5 numbers?
If so try:

Book1
	A
1	1.00105
2	1.00116
3	1.00177
4	1.00096
5	1.00108
6	1.00098
7
8	1.001208
Sheet3

Cell Formulas
Range	Formula
A8	A8	=AVERAGEIF(A1:A6,">="&LARGE(A1:A6,5))

No, you need the find the average of the six numbers then find the farthest number from that average

 

[AhoyNC]

So what would the answer be for your example?

 

[BSALV]

in my 2021, i don't have to specify, but i think it's a matrixformula, so close with CTRL+SHIFT+enter (but XL2BB also says it isn't ???)
And i made an error, formula is okay, but all numbers are multiplied !!!

Map1
	A	B	C
1	100,1050
2	100,1160
3	100,1770
4	100,0960
5	100,1080
6	100,0980
7
8	100,1167	average
9	100,1770	max
Blad1

Cell Formulas
Range	Formula
A8	A8	=AVERAGE(A1:A6)
A9	A9	=INDEX($A$1:$A$6,MATCH(MAX(ABS(A1:A6-AVERAGE($A$1:$A$6))),ABS(A1:A6-AVERAGE($A$1:$A$6)),0))

 

[jtakw]

So what would the answer be for your example?

Hi,

As @AhoyNC asked above, and also, can you please clarify...
Are you looking for the number farthest from the Average of the 6 numbers, OR
Are you looking for the Average of 5 numbers Excluding the number farthest from the average of the 6 numbers ??

 

[obiwann]

Hi,

As @AhoyNC asked above, and also, can you please clarify...
Are you looking for the number farthest from the Average of the 6 numbers, OR
Are you looking for the Average of 5 numbers Excluding the number farthest from the average of the 6 numbers ??

HI,

I am looking to find the number farthest from the average of the 6, kick that number and then find the average of the remaining 5.

 

[BSALV]

Map4
	A	B
1	1,00105
2	1,00116
3	1,00177
4	1,00096
5	1,00108
6	1,00098
7
8	1,00177	farthest
9	1,00105	new average
Blad4

Cell Formulas
Range	Formula
A8	A8	=INDEX($A$1:$A$6,MATCH(MAX(ABS(A1:A6-AVERAGE($A$1:$A$6))),ABS(A1:A6-AVERAGE($A$1:$A$6)),0))
A9	A9	=+AVERAGEIF(A1:A6,"<>"&A8,A1:A6)

 

[jtakw]

HI,

I am looking to find the number farthest from the average of the 6, kick that number and then find the average of the remaining 5.

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