We help IT Professionals succeed at work.

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

Mike Cloud
Mike Cloud asked
on
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?
Comment
Watch Question

View Solutions Only

Karen FalandaysTraining Specialist

Commented:
I would use conditional formatting. Select the data. Click Home>Conditional Formatting>Top/Bottom rules
Consultant
Commented:
Something like this?
EE.xlsx
Mike CloudCyber Analyst

Author

Commented:

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 

                                     

21 Pedro
21 LeAnn
21 Andrew
21 Kevin
25 Tucker


The above people are the bottom 20%


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


                             

1 James
2 Judy
2 Alex
4 Tony


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 FalandaysTraining Specialist

Commented:
You can modify the rules. Select the data. Home>Conditional formatting, manage rules. Now click to edit and modify the contingents to your requirements.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:

This will give the top 20

=LARGE(Range,20)

and this the bottom 20


=SMALL(Range,20)
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Ignore the "1" and ":" that the Beta generates.
Tom FarrarConsultant
Commented:
This is how I see it, but open to other thoughts.
EE.xlsx
Mike CloudCyber Analyst

Author

Commented:

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?

Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 CloudCyber Analyst

Author

Commented:

Tom


I'm still working on understanding your formula. 

Tom FarrarConsultant

Commented:
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.