RANK Function Examples In Excel, VBA, & Google Sheets

This tutorial demonstrates how to use the Excel RANK Function in Excel to rank a number within a series.
Rank Main Function

RANK Function Overview

The RANK Function Rank of a number within a series.

To use the RANK Excel Worksheet Function, select a cell and type:

Rank Function

(Notice how the formula inputs appear)

RANK function Syntax and inputs:

number – The number that you wish to determine the rank of.

ref – An array of numbers.

order – OPTIONAL. A number indicating whether to rank descendingly (0 or Ommitted) or ascendingly (non-zero number)

AutoMacro - VBA Code Generator

What Is the RANK Function?

The Excel RANK Function tells you the rank of a particular value taken from a data range. That is, how far the value is from the top, or the bottom, when the data is put into order.

RANK Is a “Compatibility” Function

As of Excel 2010, Microsoft replaced RANK with two variations: RANK.EQ and RANK.AVG.

The older RANK Function still works, so any older spreadsheets using it will continue to function. However, you should use one of the newer functions whenever you don’t need to remain compatible with older spreadsheets.

How to Use the RANK Function

Use RANK like this:

How to use RANK

Above is a table of data listing the heights of a group of friends. We want to know where Gunther ranks in the list.

RANK takes three arguments:

  • The first is the value you want to rank (we’ve set this to C10, Gunther’s height, but we could also put the value in directly as 180)
  • The second is range of data – C4:C13
  • The third is the order of the rank
    • If you set this to FALSE, 0, or leave it blank, the highest value will be ranked as #1 (descending order)
    • If you set this to TRUE or any non-zero number, the lowest value will be ranked as #1 (ascending order)

RANK determines that Gunther is the 4th tallest of the group, and if we put the data in order, we see that this is true:

IN Order

A few key points about the RANK Function:

  • When determining the order, text strings will result in a #VALUE! error
  • As you’ve just seen, you don’t need to sort the data for RANK to work correctly

How RANK Handles Ties

In the below table I’ve added a column to the table that returns the rank of each member of the group. I used the following formula:

Note that I’ve locked the data range $C$4:$C$13 by select “C4:C13” in the formula bar, and then pressing F4. This keeps this part of the formula the same so that you can copy it down the table without it changing.

Ties

We have a tie! Both Joey and Mike are 178cm tall.

In such cases, RANK assigns both values the highest rank – so both Joey and Mike are ranked 5th. Because of the tie, there is no 6th place, so the next tallest friend, Phoebe, is in 7th place.

How to Use RANK.EQ

RANK.EQ works in the same way as RANK. You use it like this:

How to use RANK EQ

As you can see here, with RANK.EQ you define exactly the same arguments as with RANK, namely, the number you want to rank, the data range, and the order. We’re looking for Gunther’s rank again, and RANK.EQ returns the same result: 4.

RANK.EQ also handles ties in the same way as RANK, as shown below:

RANK.EQ-Ties

Again, Joey and Mike are tied at 5th place.

How to Use RANK.AVG

RANK.AVG is very similar to RANK.EQ and RANK. It only differs in the way it handles ties. So if you’re just looking for the rank of a single value, all three functions will return the same result:

How to use RANK.AVG

Once again, the same result – 4th place for Gunther.

Now let’s look at how RANK.AVG differs in terms of ties. So this time I’ve used this function:

And here are the results:

RANK.AVG Ties

Now we see something different!

RANK.AVG gives Joey and Mike the same rank, but this time they are assigned the average rank that they would have received had their heights not been equal.

So, they would have been ranked 5th and 6th, but RANK.AVG has returned the average of 5 and 6: 5.5.

If more than two values are tied, the same logic applies. Let’s pretend Phoebe has a sudden growth spurt, and her height increases to 178cm overnight. Now RANK.AVG returns the following:

RANK.AVG 3 Ties

All three friends how rank 6th: (5 + 6 + 7) / 3 = 6.

RANK IF Formula

Excel doesn’t have a built-in formula that enables you to rank values based on a given criteria, but you can achieve the same result with COUNTIFS.

Say the friends want to create two separate rank orders, one for males and one for females.

Here’s the formula we’d use:

Rank IF

COUNTIFS counts the number of values in a given data range that meet criteria you specify. The formula looks a little intimidating, but it makes more sense if we break it down line-by-line:

So the first criteria we’ve set is that the range in C4:C13 (again, locked with the dollar signs so that we can drag the formula down the table without that range changing) must match the value in C4.

So for this row, we’re looking at Richard, and his value is C4 is “Male”. So we’re only going to count people who also have “Male” in this column.

The second criteria is that D4:D13 must be higher than D4. Effectively, this returns the number of people in the table who’s value in the D column is greater than Richard’s.

Then we add 1 to the result. We need to do this because no one is taller than Richard, so the formula would return 0 otherwise.

Note that this formula handles ties in the same way as RANK.EQ.

Learn more on the main page for the Excel COUNTIF Function.

RANK Function in Google Sheets

The RANK Function works exactly the same in Google Sheets as in Excel:

Rank Google Function

RANK Examples in VBA

You can also use the RANK function in VBA. Type:
application.worksheetfunction.rank(number,ref,order)

Executing the following VBA statements

will produce the following results

Vba rank function


For the function arguments (number, etc.), you can either enter them directly into the function, or define variables to use instead.

Return to the List of all Functions in Excel

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!