How to calculate rank with a condition in Excel? RANK IF

Posted on

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.

Permanent link: https://indzara.com/faq/how-to-calculate-rank-if-with-a-condition-in-excel/

 

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.

 

Input Data
Input Data

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

 

Convert to table
Convert to table

 

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)

Formula for Overall Rank with RANK function
Formula for Overall Rank with RANK function

Tip: If we need to find the rank based on lowest, use 1 instead of 0 in the function for the Order parameter.

Results of Overall Rank
Results of Overall Rank

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.

Countifs Function to calculate rank
Countifs Function to calculate rank

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

Formula to calculate RANK with condition
Formula to calculate RANK with condition

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 table by Score
Sorting table by Score

Sorting by Department and Score from Largest to Smallest,

Sorting table by Department and Score
Sorting table by Department and Score

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.

Ranking in case of tie
Ranking in case of tie

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.