Avatar of Mike Cloud
Mike Cloud
Flag for United States of America asked on

Trying to determine top 20 and bottom 20 percent of a group of 23

Trying to determine top 20 and bottom 20 percent

I have 23 people.  They are ranked in order of priority below.

1-James
2-Judy
2-Alex
4-Tony
5-Terry
5-Lisa
7-Paul
7-Sandy
10-Felix
12-Don
12-Ned
12-Alfred
12-Edward
12-Bob
17-Tim
17-Raymond
17-Chris
20-Nelson
21-Pedro
21-LeAnn
21-Andrew
21-Kevin
25-Tucker

Is something than can be done in excel? Or perhaps some simple formula on a calculator?
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Tom Farrar

8/22/2022 - Mon
Karen Falandays

I would use conditional formatting. Select the data. Click Home>Conditional Formatting>Top/Bottom rules
ASKER CERTIFIED SOLUTION
Tom Farrar

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Mike Cloud

ASKER

Ok. This is great, and it's close. However, the top performers would be the lower numbers. When I use the top/bottom rules, the top 20% are 

                                     

21Pedro
21LeAnn
21Andrew
21Kevin
25Tucker


The above people are the bottom 20%


The top people actually those rated with lower numbers, with 1 being the top priority


                             

1James
2Judy
2Alex
4Tony


If the count was the same, I'd like to say they're actually the top 20 and bottom 20. With the diff on count, I'm unsure...?

Karen Falandays

You can modify the rules. Select the data. Home>Conditional formatting, manage rules. Now click to edit and modify the contingents to your requirements.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Martin Liss

This will give the top 20

=LARGE(Range,20)

Open in new window

and this the bottom 20


=SMALL(Range,20)

Open in new window

Martin Liss

Ignore the "1" and ":" that the Beta generates.
SOLUTION
Tom Farrar

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mike Cloud

ASKER

Thanks Karen


I'm trying to figure out the correct rule to place the top and bottom 20 %  to as those with the lower and higher numbers in the priority column. 



Tom


I'm sure this is a great way to break it down. To me though, it's latin. Can you explain what you're formulas are doing? Sorry... I wish I knew excel. I apparently need a class



Marin


How would I apply this to my current workbook?

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

How would I apply this to my current workbook?
I'm sorry but I missed the word "percent" in your question and the formulas I gave select the largest and smallest values.
Mike Cloud

ASKER

Tom


I'm still working on understanding your formula. 

Tom Farrar

The formula I wrote is based upon 1-25 ratings.  A rating of 5 would be 20% of 25 or 5/25 (which is 1/5, which is 20%).  Since the lower ratings are the best, then 100%-20% leaves the other 80%.  On the other side, the rating of 20 shows 80% better, based on 20/25, which is 4/5, which is 80%.   Column G in my last example shows the declining % from the 1-to-25 rating.  There might be some math majors out here that would disagree with how I came up with the top and bottom 20% based on rating, and I would love for others to chime in.
Your help has saved me hundreds of hours of internet surfing.
fblack61