Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Bottom 30 percent

Status
Not open for further replies.

deedar

Programmer
Aug 23, 2007
45
PK
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.
 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top