?
Solved

Top 10 bottom 10% data

Posted on 2014-09-29
6
Medium Priority
?
160 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
6 Comments
 
LVL 20

Assisted Solution

by:ltlbearand3
ltlbearand3 earned 600 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 54

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 700 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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
LVL 27

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 27

Accepted Solution

by:
ProfessorJimJam earned 700 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

589 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