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

one way is to use auto filter on column A
set to 10 %max
select the cell in column a and paste into column B just below the last data
point.
in Column B enter something like "*" next to all of the cells in A with data
set to 10 % min
again select all the cells showing in column A an paste below the lowest
point in column B
select all the rows showing data in A and edit-delete rows.
turn off auto select
use auto select on Column B
and select "*"
select all the rows with the "*" and edit-delete rows
turn off the auto select
select all the empty cells above the first cell with data in column B
Edit-delete cell-Move up
It is easier to do than to explain

"Eric" wrote:

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

Thank you for your reply

The function of auto filter is good suggestion, but the sorting cannot be
updated automatically as any value from the list changed without clicking the
filter again.
Do you have any suggestion on solving the updated issue as any value from
the list changed?
Thank you for your suggestion
Eric

"bj" wrote:

one way is to use auto filter on column A
set to 10 %max
select the cell in column a and paste into column B just below the last data
point.
in Column B enter something like "*" next to all of the cells in A with data
set to 10 % min
again select all the cells showing in column A an paste below the lowest
point in column B
select all the rows showing data in A and edit-delete rows.
turn off auto select
use auto select on Column B
and select "*"
select all the rows with the "*" and edit-delete rows
turn off the auto select
select all the empty cells above the first cell with data in column B
Edit-delete cell-Move up
It is easier to do than to explain

"Eric" wrote:

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

"Eric" wrote:
.. any suggestion on solving the updated issue as any value from
the list changed?

(As responded to your multi-post in .worksheet.functions.
Pl refrain from multi-posting)

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thank everyone for suggestions
Eric

"Max" wrote:

"Eric" wrote:
.. any suggestion on solving the updated issue as any value from
the list changed?

(As responded to your multi-post in .worksheet.functions.
Pl refrain from multi-posting)

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---