How to Use RANK Function in Excel (With 5 Examples)

Using RANK Function to Break Ties

The simplest technique to establish a number’s relative position in a list of numbers is to sort the list in descending (from largest to smallest) or ascending order (from smallest to largest.

In this article, I’ll focus on the ways of sorting using the RANK function in Excel from different aspects. Before, I’ll show the basics of the function.

RANK Function in Excel (Quick View)

RANK Function in Excel (Quick View)

Download Excel Workbook

Excel RANK Function: Syntax & Arguments

Firstly, you’ll see the syntax and argument of the function. If you insert the function after entering equal sign (=), you’ll see the following figure.

RANK Formula

Summary

The RANK function returns the position of a given number in a given list of other numbers.

Syntax

=RANK (number, ref, [order])

Return Value

Rank number

Arguments

Argument Required or Optional Value
number required The number that you want to rank
ref required The reference (an array or a list of numbers) containing the number
order optional The way of ranking (descending or ascending order)

Note.

  1. Microsoft warns that the RANK function may not be available in the future as they developed more new and better functions for ranking with better accuracy and usage.
  2. In the case of order argument, 0 is used for descending order and 1 is used for ascending order. If you omit the order (as it is an optional argument) while inserting the RANK function, the function will sort automatically in descending order.

How to Use the RANK Function in Excel (With 5 Examples)

Example 1: Using RANK Function in Descending Order

Let’s imagine, we have a dataset where exam scores are given to students. Now, we will find the rank based on the scores. Firstly, we will learn how to rank in descending order.

Just insert the following formula.

=RANK(C5,$C$5:$C$15,0) 

Here, C5 is a score, C5:C15 is the list of scores, 0 is for the descending order.

Note. You must use the dollar sign ($) inside the list of scores to get an accurate ranking.

Using RANK Function in Descending Order

Example 2: Using RANK Function in Ascending Order

Again, we will find the rank for the exam scores in ascending order. Here, the formula will be the same except 1 will be used for the ascending order.

=RANK(C5,$C$5:$C$15,1)

Using RANK Function in Ascending Order

Example 3: Using RANK Function in Non-Contiguous Cells

Sometimes you have to face a situation where to rank blank cells or non-contiguous cells.

In that case, you may have to use the following formula.

=IFERROR(RANK(C5,($C$5,$C$6,$C$9:$C$12),0),"")

Here, the IFERROR function is used to avoid non-ranked cells.

Using RANK Function in Non-Contiguous Cells

Example 4: Getting Unique Rank Using RANK Function

If two numbers are the same, the RANK function automatically returns a duplicate rank for the numbers. For example, if two distinct students get the same marks (see the following figure), you will find duplicate ranks for their scores.

Duplicate Rank in Excel

How can we solve the problem?

=RANK(C5,$C$5:$C$15,0)+COUNTIF($C$5:C5,C5)-1

Here, the syntax COUNTIF($C$5:C5,C5) counts 2 for the second score (C7 in the dataset) of the two same scores. But the syntax counts 1 for all scores.

Later, after subtracting 1, we can easily get a unique rank.

Getting Unique Rank Using RANK Function

Example 5: Using RANK Function to Break Ties

In certain cases, you cannot apply the previous method to get a unique rank.

Assuming that the number of time (in minutes) that students take to finish the exam are given.

If any student takes less time in an exam to finish, he or she will be ahead of the other who has the same score but takes more time.

Now, insert the following formula.

=IF(COUNTIF($C$5:$C$15,C5)>1,RANK(D5,$D$5:$D$15,1)/100,0)

Here, the COUNTIF($C$5:$C$15,C5)>1 counts the ties.

Then the RANK(D5,$D$5:$D$15,1) sorts the time in ascending order (as less time is the criteria for a better rank) for the ties.

Later, we have to divide by 100 to get the output in decimal value.

Using RANK Function to Break Ties

After entering the formula, you’ll get the following output.

Using RANK Function to Break Ties

Lastly, we have to determine the final rank. For this, just sum the tied rank and tie break.

=E5+F5

Now, see the following figure that shows James is ahead of Michael as James takes less time in the exam to finish.

Using RANK Function to Break Ties

Common Errors While Using the RANK Function

Common Errors When they show
#N/A Occurs when the given number that you want to find rank is not available in the reference (the list of numbers).

Conclusion

This is how you can apply the RANK function to get the row number. If you have an interesting and unique method of using the RANK function, please share it in the comments section below.

Thanks for being with me.


Further Readings

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo