Chandoo recently posted a challenge to help a reader with a problem.
The problem is: how do you highlight performance rating in a list of employee according to percentile?
Shelly, an HR Manager, wants to divide her list of employees highlighting those who belong in the Top 5% then the next 15%, the next 65%, the next 15% and the bottom 5%.
She needs an efficient way of doing this as it needs to be done for hundreds of different departments.
Both Chandoo and his readers have already posted the solution so I am not creating anything new.
Rather, in this article and in the embedded video I am showing you how to do it and I include an additional way of categorizing the information which I think is really useful.
The starting point can be seen in the following image.
The list named ‘Input’ needs to be turned into ‘Output’ i.e. assigning conditional formatting to it so that each employee and his/her Performance Rating is shaded in a color which depends on the rating itself.
The color selection can be seen in the Percentile Thresholds table.
We need to work out the numeric percentile thresholds by applying the PERCENTILE function to the range of Performance Ratings at our disposal.
The Syntax for the formula is ‘=PERCENTILE(array,k)‘
In our particular case the array is $F$6:$F$33 and the the k value is ’1 less the cumulative % of employees for specific range’.
So if the employee‘s Performance Rating is equal or above 4, he or she will belong to the Top 5%. If his/her PR score is between 3.46 and 4, he or she will belong to the following 15% and so on.
The next step is to apply conditional formatting to the table of employee names and performance rating using the individual rating to decide which color shading the cell should have.
We do this by creating 5 rules which check the Performance Rating value against each threshold.
If the condition is satisfied then the formatting is applied otherwise Excel progresses onto the next rule until all 5 five are taken into consideration.
This produces the result of highlighting each individual employee’s performance using a color coding.
One thing I believe would make the information more usable is the ability to filter the list of employees based on Performance Rating Categories.
I do this by adding a ‘Performance Category’ value next to the threshold classification which we created earlier.
Next I add a ‘Performance Category’ column in the Employee table and I assign values using an IF statement which evaluates the PR value and compares it to the Threshold values.
After applying filters we can then select, for instance, only Employees belonging to Categories D and E (the bottom 20%) to identify who needs extra training.