Skip to content

Learn About the Rank Function

This article is all about the RANK function.

What is the RANK function? It is a function that allows you to rank values in order from largest to smallest or smallest to largest.

Let’s say you have the following dataset.

You can use the RANK function to rank each value in the dataset. The largest value receives a rank of 1. The next largest receives a rank of 2. And so on and so forth.

You can also set up the RANK function to give the smallest value a rank of 1, and the next smallest a rank of 2, etc.

You can set it up either way.

How to Use the RANK Function

The RANK function has two required arguments and one optional argument. They are:

  • Number – The number or a reference to the number you want to rank (required)
  • Ref – An array of numbers or a reference to a range containing numbers (required)
  • Order – May be 1 or 0. Used to indicate if the largest number gets a rank of or if the smallest number gets a rank of 1 (optional – defaults to 0)

Here is how you use the function and its arguments in Excel.

=RANK(Number, Ref, Order)

The first argument references the value you wish to rank.

The second argument is the range of numbers compared against the first argument to determine the rank.

The third argument can be a 1 or 0. In this case, it is set to zero to indicate that the largest number receives a rank of 1.

Rank Multiple Values

If you wish to rank multiple values, then it’s a good idea to make the second argument an absolute reference.

This will allow you to use the fill handle to drag the function down the column to rank all your numbers.

The RANK.AVG Variant

The RANK.AVG function is the same as the RANK function but with one major difference.

The average rank is returned if two numbers share the same rank.

This is different because the RANK function will simply assign the top rank shared by any values that match.

Take a look at the comparison below.

The RANK function assigns the rank of 3 to both tens, while the RANK.AVG function assigns the rank of 3.5 instead.

3.5 is assigned because this is the average of the rank values 3 and 4.

The RANK.EQ Variant

There is also a RANK.EQ function. This function works the same way as the original RANK function.

The only difference is that it was introduced in Excel 2010 and is therefore a newer function.

And that about sums up how you can use the RANK function in Microsoft Excel.

This Post Has One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top