Bottom 30 percent

[deedar]

In an Excel Sheet, I have a list of employees and their final scores out of 100 in annual performance evaluation. There are twenty employees in the list and data values are as follows:

Column A:
A3 = Appraised
A4 = Emp1
A5 = Emp2
A6 = Emp3
A7 = Emp4
...
...
A23 = Emp20

Column B:
B3 = Total Score
B4 = 92
B5 = 83
B6 = 81
B7 = 78
B8 = 73
B9 = 66
B10 = 63
B11 = 63
B12 = 61
B13 = 57
B14 = 53
B15 = 52
B16 = 49
B17 = 48
B18 = 48
B19 = 34
B20 = 29
B21 = 25
B22 = 16
B23 = 13

In column C, I applied Rank function:
C3 = Rank
C4 = =RANK(B4,$B$4:$B$23)
...
...
C23 = =RANK(B23,$B$4:$B$23)

What I have to submit to senior management is:
1. Top 30 percent employees based on the Total Score. They will be given A rating.
2. Bottom 30 percent employees based on the Total Score. They will be given C rating
3. Remaining employees will be given B rating

There are 20 employees altogether. So, 30 percent of 20 are 6. Based on the rank, employees with ranks 1,2,3,4,5 and 6 will get A rating. But the issue is to find out bottom 30 percent employees. Employees with ranks 20,19,18,17,16 and 15 would have got C rating but there is a tie at rank number 14. Two employees have got rank number 14 and there is no rank number 15.

I used Data -> Filter -> Auto Filter.
In column B (Total Score), I selected Bottom 30 percent and it showed me the following rank numbers:
20, 19, 18, 17, 16, 14, 14.

I am not sure whether two employees having rank number 14 should be included in bottom 30 percent or not.

 

[GlennUK]

You have to decide what logic is to apply when a tie causes problems. Is there any other criteria that could separate employees between ratings other than appraisal score?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[deedar]

Thanks.

There is no other criteria that could separate employees between ratings other than appraisal score.

I consulted with senior management and it was agreed not to exceed the threshold value for A and C rating in any case. So, it was finalized to include two employees having rank number 14 in B rating.