We help IT Professionals succeed at work.

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

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

Training Specialist

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

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...?

Training 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.
"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)`
"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.
Consultant
Commented:
This is how I see it, but open to other thoughts.
EE.xlsx
Cyber Analyst

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?

"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.
Cyber Analyst

Commented:

Tom

I'm still working on understanding your formula.

Consultant

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.