Purpose
Return value
Syntax
=RANK(number,ref,[order])- number - The number to rank.
- ref - The range that contains numbers to rank against.
- order - [optional] Whether to rank in ascending or descending order.
How to use
The Excel RANK function assigns a rank to a numeric value when compared to a list of other numeric values. Use RANK when you want to display a rank for numeric values in a list. It is not necessary to sort the values in the list before using RANK.
Controlling rank order
The rank function has two modes of operation, controlled by the order argument. To rank values where the largest value is ranked #1, set order to zero (0). For example, with the values 1-5 in the range A1:A5:
=RANK(A1,A1:A5,0) // descending, returns 5
=RANK(A1,A1:A5,1) // ascending, returns 1
Set order to zero (0) when you want to rank something like top sales, where the largest sales number should rank #1, and to set order to one (1) when you want to rank something like race results, where the shortest (fastest) time should rank #1.
Duplicates
The RANK function will assign duplicate values to the same rank. For example, if a certain value has a rank of 3, and there are two instances of the value in the data, the RANK function will assign both instances a rank of 3. The next rank assigned will be 5, and no value will be assigned a rank of 4. If tied ranks are a problem, one workaround is to employ a tie-breaking strategy.
Note: The RANK function is now classified as a compatibility function. Microsoft recommends RANK.EQ or RANK.AVG be used instead.
Notes
- The default for order is zero (0). If order is 0 or omitted, number is ranked against the numbers sorted in descending order: smaller numbers receive a higher rank value, and the largest value in a list will be ranked #1.
- If order is 1, number is ranked against the numbers sorted in ascending order: smaller numbers receive a lower rank value, and the smallest value in a list will be ranked #1.
- It is not necessary to sort the values in the list before using the RANK function.
- In the event of a tie (i.e. the list contains duplicates) RANK will assign the same rank value to each set of duplicates.
- Some documentation suggests ref can be a range or array, but it appears ref must be a range.
RANK function examples
Sort text and numbers with formula
Rank without ties
Rank if formula
Rank and score with INDEX and MATCH
Rank race results
Basic text sort formula
Rank function example
Basic numeric sort formula
RANK function videos
How to rank values with the RANK function
How to pick names out of a hat with Excel
Related functions
SMALL Function
The Excel SMALL function returns a numeric value based on its position in a list when sorted by value in ascending order. In other words, SMALL can return the "nth smallest" value (1st smallest value, 2nd smallest value, 3rd smallest value, etc.) from a set of numeric data.
LARGE Function
The Excel LARGE function returns a numeric value based on its position in a list when sorted by value in descending order. In other words, LARGE can retrieve the "nth largest" value – 1st largest value, 2nd largest value, 3rd largest value, etc.
RANK.EQ Function
The Excel RANK.EQ function returns the rank of a number against a list of other other numeric values. When values contain duplicates, RANK.EQ will assign the higher rank to each set of duplicates.
RANK.AVG Function
The Excel RANK.AVG function returns the rank of a number against a list of other other numeric values. When values contain duplicates, the RANK.AVG function will assign an average rank to each set of duplicates.
