# Top 10 bottom 10% data

Hi,

I have a data set where i want to analyse the data excluding the top/bottom 10%, im thinking to rank the data and then have some formula which says if >10% & <90% flag it, then put that as a filter on a pivot

Im not sure what formula i would use to rank the data, anyone have any suggestions how to approach this analysis?

Thanks
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
When you say you have a data set, you may need to define this more as that can mean many different things.  Since you list this under Excel, I am going to assume your data is in an Excel Spreadsheet and is all in one column.

If that is the case you can just use Conditional Formatting as that has built in tools to help.

Depending on your version of Excel here is what you can do:
1.  Highlight the data
2.  Click on the Conditional Formatting button (Home Tab of the ribbon)
3.  Select Top/Bottom Rules
4.  Select Top 10 %
6.  Select Top/Bottom Rules
7.  Select Bottom 10 %

Now you can also add a filter and then filter by color to just see the top 10% or the bottom 10%.
0
Author Commented:
Yep, it is excel, there are multiple columns, but only one im basing the rank on. So my top/bottom 10% are highlighted, the next step is to flag them so i can exclude them from a pivot, have you any formula that says if cell is highlighted return "1"

Thanks
0
Commented:
Hi,

pls try

=PERCENTILE.EXC(\$A\$3:\$A\$11,0.1)
and
=PERCENTILE.EXC(\$A\$3:\$A\$11,0.9)

see example

Regards
EE20140929.xlsx
0
Commented:
assuming your Data is in column A  select the whole column A then

put the following formula in the conditional formatting   with formatting a color for example red.

=IF(OR(\$A2 < PERCENTILE(\$A:\$A,0.1),\$A2 > PERCENTILE(\$A:\$A,0.9)),1,"")

it will then highlight all of those cells that you want to have them excluded.

see the attached example file.
Remove-top-and-bottom-10--from-data.xlsx
0
Commented:
then you can filter the data by color and delete those one that you do not need.
the attached example also shows how to do it with formula in cell without conditional formatting.
0

Experts Exchange Solution brought to you by