In this blog post, we will learn how to calculate Rank with a condition. As Excel does not have a RANKIF function yet, we will use a different method to arrive at the solution.
VIDEO DEMO
If you would like to be notified of these videos, please subscribe to our YouTube channel.
DATA
Let’s start with the input data. This is for a list of 10 employees, where the Department Name and the employee’s score are provided.
Let’s assume that the score is out of max of 10, higher the number, the better it is. We need to find the rank of each employee within the Department that the employee belongs to.
HOW TO CALCULATE OVERALL RANK AND RANK IF
Step 1: Convert the data to a Table by pressing Ctrl+T
Step 2: Calculate Overall Rank with RANK function
First, let’s calculate the overall rank (rank of an employee within the company).
We can use the RANK function in our formula.
General Syntax
= RANK( number, ref, [order])
In our context
= RANK(“Specific Employee’s Score”, “List of All Employees’ Scores”,0)
0 is to indicate that we need the rank based on descending order (highest is first rank)
Tip: If we need to find the rank based on lowest, use 1 instead of 0 in the function for the Order parameter.
That was straightforward, as we have a function RANK specifically designed for this use case.
Step 3: Calculate Overall Rank without RANK function
If we need to determine rank within Department – for example, rank within Finance, or rank within HR, then it is not so straight forward, as we don’t have a RANKIF function. ?
Not a problem. Excel has enough functions for us to find a solution to this.
If we think about what ranking does, first rank means that there is no other number greater than that. We can translate this to
If number of values greater than the current value = 0, then that is the highest number. In other words, Rank = 1
If number of values greater than the current value = 1, then that is the second highest number. In other words, Rank = 2
We can convert this to a formula as below.
=COUNTIFS([Score],”>”&[@Score])+1
We must add 1 at the end of the formula because for the first rank, there are 0 values greater.
As you can see above, the two formulas give us the same result.
Step 4: Calculate Rank within Department
Now, we need to modify second formula to calculate rank within each department.
The COUNTIFS function allows us to provide multiple conditions (unlike the RANK function). So, we can add Department condition to the same formula.
=COUNTIFS([Score],”>”&[@Score],[Department],[@Department])+1
Andrea has the top score in Finance, while Cathy has the top score in HR.
We can quickly verify this by using the Sort options.
Sorting just by Score from Largest to Smallest,
Sorting by Department and Score from Largest to Smallest,
Formula results match with the Sorting results. ?
Tie
There are scenarios where we may have a tie in score. If two employees had same top score, this formula will assign same rank 1 to both the employees, and there will be no second rank. The next employee will get the 3rd rank.
There are scenarios where we may need to handle ties differently. Those will need tweaks to this formula. We can take those up in a future video.