Excel RANK Function and Formula in Excel with Examples

How to Use the RANK Function and Formula in Excel

In this tutorial, you'll learn how to use the RANK function and formula in Excel. 

RANK() is a function to get the rank of a number in a group of numbers.

RANK Syntax

The syntax of the RANK() function is as follows:

=RANK(number, ref, [order])

Arguments

  • number: The number that you to find the rank
  • ref: The reference to a range of cells containing numbers, or array of a list of numbers
  • order: A number whether to rank in ascending or descending order

Return Value

The number representing the rank of a number.

Remarks

  • number and ref are required.
  • RANK() ignores non-numeric values in ref
  • order is optional. If order is set to 0 or omitted, RANK() ranks a number in descending order. If order is any nonzero value, RANK() ranks a number in ascending order.

RANK Function Examples

Demo data:

 ABC
1Group 1Group 2Group 3
21011
31522
41433
51333
62244
71954
82565
93076
103287
112498

Find the rank of a number in a list sorted in descending order

To get the rank of a number in a list of numbers sorted in descending order, you set the order argument to 0 or leave it default. 

Here is an example:

=RANK(13, A2:A11, 0)

Or:

=RANK(13, A2:A11)

RANK() ranks 13 in a list of numbers below in the range A2:A11 and returns 9.

(10, 15, 14, 13, 22, 19, 25, 30, 32, 24)

The numbers in the list sorted in descending order are as follows:

(31, 30, 25, 24, 22, 19, 15, 14, 13, 10)

As you can see, number 13 is located in the 9th position of the list.

Find the rank of a number in a list sorted in ascending order

To get the rank of a number in a given list sorted in ascending order, set the order argument to a nonzero value, as the following example shows:

=RANK(13, A2:A11, 2)

RANK() returns 2, which is the rank of number 13.

The numbers in the list sorted in ascending order are as follows:

(10, 13, 14, 15, 19, 22, 24, 25, 30, 32)

As you can see, number 13 is located in the 2nd position of the list.

Duplicate numbers in a list

The following example returns the rank of a number in the list containing duplicate numbers:

=RANK(4,C2:C11,1)

The above formula returns 5, which is the rank of number 4 in the range C2: C12.

As you can see, there are two occurrences of 3s on the list.

Another example:

=RANK(5,C2:C11,1)

The above formula returns 7, which is the rank of 5 in the range C2: C11. There are two occurrences of 3s and two occurrences of 4s, so number 5 is located in 7th position.

Note that duplicate numbers get the same ranks, and the presence of duplicate numbers affects the ranks of subsequent numbers.

In this tutorial, you've learned how to use the Excel RANK function

RANK() is a function to get the rank of a number in a list of numbers. 


See also:
Excel ROUNDUP Function with Examples
Excel MIN Function with Examples
Excel COUNTA Function – Count Non-Blank Cells in Excel
Excel COUNTBLANK Function – Count Blank Cells in Excel
Excel SUMIFS Function – Multiple Criteria

Leave a Comment