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:
A | B | C | |
---|---|---|---|
1 | Group 1 | Group 2 | Group 3 |
2 | 10 | 1 | 1 |
3 | 15 | 2 | 2 |
4 | 14 | 3 | 3 |
5 | 13 | 3 | 3 |
6 | 22 | 4 | 4 |
7 | 19 | 5 | 4 |
8 | 25 | 6 | 5 |
9 | 30 | 7 | 6 |
10 | 32 | 8 | 7 |
11 | 24 | 9 | 8 |
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.