Solved

Top 10 bottom 10% data

Posted on 2014-09-29
6
152 Views
Last Modified: 2014-09-29
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
0
Comment
Question by:Seamus2626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 20

Assisted Solution

by:ltlbearand3
ltlbearand3 earned 150 total points
ID: 40349838
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 %
5.  Select your formatting style
6.  Select Top/Bottom Rules
7.  Select Bottom 10 %
8.  Select your formatting style

Now you can also add a filter and then filter by color to just see the top 10% or the bottom 10%.
0
 

Author Comment

by:Seamus2626
ID: 40349915
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
 
LVL 51

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 175 total points
ID: 40349943
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40349954
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
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 175 total points
ID: 40349981
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
 

Author Closing Comment

by:Seamus2626
ID: 40350244
Thanks guys!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question