reject the bigest value and promt fot the best two

[Guest]

i have the follow problem, the A collum holds the name, each name have up to
seven posiotions for values. Now i want to reject the value that is not close
to the others; from the seven {he probably might have less values from seven)
and then propose the best two from them.

i have figure out the the help of the Geomean function will give me the
criteria and the absolute difference in order to reject the value that
doesnt follow the patern. but how i could make to propose the best two? {not
the two smallest or the max but the middle!}

thank you

 

[Frank Kabel]

Hi
not completely sure which values you want to select and what you want
to calculate with them. Could you give an example with your expected
result?

 

[Guest]

well here is an example: from the following numbers 101 - 87 - 168 - 145 -
211 - 1978 the number that dosen't correspont to the others is the 1978 so
the problem is that i need to find a formula to identify the number, and next
to extract the geomean from the rest, finally to propose those 2 that are
near the middle? {or lets say to exclude the smallest and the biggest values
until 2 results remain!}

thank you for your time!

PS. the 1978 number could well be something like 5

 

[Frank Kabel]

Hi
one way exluding the biggest and smalles values:
you may try the following formulas (A1:A10 contain your data):
=SMALL(A1:A10,INT(COUNTA(A1:A10)/2))
and
=SMALL(A1:A10,INT(COUNTA(A1:A10)/2)+1)

But there're other ways to get your two values depending on your chosen
algorithmn and HOW you want to calculate the middle (e.g which values
should be excluded before calculating the middle/geomean, etc.)