How to remove the top & bottom 10% from a list?

[Guest]

Referring to the post in General Question

Does anyone know how to remove the top & bottom 10 % from a list? such as
There is a list of values under column A, the number of values is not
defined, it can be as small as 10 values, and it can be as large as 6000
values and I would like to remove the 10% of maximum values and 10% of
minimum values from the lists, and store those numbers in column B.
Does anyone have any suggestion?
Thank you in advance
Eric

 

[Guest]

When you say top and bottom 10% are you talking about 10% of the total? Or
10% as in number of values? (For example if your sample is 100 do you want
the lowest 10 values and the highest 10 values removed?)

 

[Guest]

For example if the sample is 100 I want to remove the lowest 10 (10% of 100)
values and the highest 10 (10% of 100) values from the lists.

If the sample is 9, then I want to remove the lowest 1 (10% of 9) values and
the highest 1 (10% of 100) values from the lists by rounding up the number,
but I don't want to apply the function round in excel. If the number of
sample to be removed from the lowest is 1.1, then I will remove 2 samples
from the lowest.

Do you have any suggestion to solve this formula?
I look forward to your reply
Thank you very much
Eric

 

[Guest]

Here's one formulas play which might deliver it dynamically ..

Assuming source numbers listed in A1:A20

Put in B1:
=IF(A1="","",IF(OR(PERCENTRANK($A$1:$A$20,A1)<=10%,PERCENTRANK($A$1:$A$20,A1)>=90%),"",ROW()))

Put in C1:
=IF(ROW()>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW())))
Select B1:C1, copy down to C20. Col C returns the required results, ie the
list from col A less the top & bottom 10% of values. Adapt the ranges to suit.