How to use the RANK function
What is the RANK function?
The RANK function calculates the size of a specific number compared to a list of numbers. It allows you to rank numbers relative to their sizes in ascending or descending order.
There are newer better functions that may have improved accuracy, this function is available for compatibility with Excel 2007 and earlier versions.
This function is outdated and was replaced with RANK.AVG function and the RANK.EQ function. The RANK function may not be available in future Excel versions and Microsoft recommends using the newer functions from now on.
What are the differences between the RANK, RANK.EQ and RANK.AVG functions?
First, the RANK function is a legacy function and should be avoided if possible. The RANK.EQ function and the RANK function rank numbers based on size, however, they share the rank if two or more numbers are equal. For example:
| Numbers | RANK.EQ | RANK |
| 10 | 4 | 4 |
| 20 | 2 | 2 |
| 20 | 2 | 2 |
| 30 | 1 | 1 |
The RANK.AVG function returns the rank of a number in a list of numbers. If more than one value share the same rank, the average rank is returned. For example:
| Numbers | RANK.AVG |
| 10 | 4 |
| 20 | 2.5 |
| 20 | 2.5 |
| 30 | 1 |
What are the differences between the RANK and PERCENTRANK functions?
The PERCENTRANK function calculates the percent rank of a given number compared to the whole data set.
| Numbers | RANK | PERCENTRANK |
| 10 | 4 | 0 |
| 20 | 2.5 | 0.333 |
| 20 | 2.5 | 0.333 |
| 30 | 1 | 1 |
The PERCENTRANK function is also a legacy function and should be avoided if possible. Use the PERCENTRANK.EXC or the PERCENTRANK.INC.
Other ways to rank numbers in a list?
Data Bars is a conditional formatting feature that allows you to create horizontal bars that represent a corresponding number. This allows you to more quickly scan a group of numbers for the largest and smallest numbers.
Color scales is also a conditional formatting feature that allows you to highlight cells based on the size of their numbers.
Why is ranking numbers useful in statistics?
Ranking numbers in statistics refers to converting data values into their ranked order.
- Reduces the impact of outliers
Extreme values don't distort ranks as much as raw scores. - Easier to interpret
Ranks provide a sense of relative standing on a consistent scale from lowest to highest. - Allows non-parametric tests
Statistical tests that don't rely on parameterized distributions. - Simplifies combining scores
Ranks provide a common scale for scores from different sources. - Assesses relative performance
Ranks indicate how a value compares to others in the dataset. - Analyze changes in rank
Shifts in ranks over time can be meaningful even if values don't change much. - Identify top/bottom performers
Ranks make it easy to find top and bottom values. - Tied ranks for equal values
Accounts for duplicate values getting the same mean rank.
RANK Function Syntax
RANK(number,ref,[order])
RANK Function Arguments
| number | Required. The number you want to calculate the rank for. |
| Ref | Required. An array or cell reference to a list of numerical values. |
| Order | Optional. How to rank the number, 1 - ascending or 0 - descending. If omitted - descending order. |
What is ascending?
Ascending is a term used to describe the order and sequence of data. Ascending order sorts data from lowest to highest.
Arranging data points from lowest to highest value. Also known as increasing order. The ranking goes up, with smaller values first.
Example: Sorting numbers 1, 2, 3 in ascending order.
What is descending?
Descending is a term used to describe the order and sequence of data. Descending order sorts data from highest to lowest.
Arranging data points from highest to lowest value, also known as decreasing order. The ranking goes down, with larger values first.
Example: Sorting numbers 3, 2, 1 in descending order.
RANK Function example
The image above shows 5 digit numbers in cell range B3:B15, the corresponding cells on the same row in column C shows the rank output from the RANK function. The RANK function returns a number representing the position if they were sorted in ascending or descending order.
Formula in cell C3:
The largest number is in cell B15, cell C15 contains the output from the RANK function which is 1. The smallest number is in cell B12, cell C12 shows 13 which is the rank. There are 13 numbers in cell range B3:B15 and the rank 13 tells us that the number is the smallest based on it's size compared to the other numbers in the list.
RANK Function alternative
The COUNTIF function allows you to count cells based on a condition. The smaller than or larger than character lets you count numbers that are smaller or larger respectively.
Formula in cell C3:
The COUNTIF function calculates the number of cells that is equal to a condition.
Function syntax: COUNTIF(range, criteria)
For example, cell range B3:B15 contains five numbers larger than the number in cell C3. The COUNTIF function returns five indicating that the number in cell B3 is the sixth largest number, the reason why is because the COUNTIF function returns 0 (zero) for the largest number in the group. One for the second largest and so on.
To make the COUNTIF function return the same number as the RANK function then add 1 to the function like this:
Why is the COUNTIF function returning 0 (zero) for the largest number in a list?
The COUNTIF function counts the number of cells that contain a number larger than the condition.
For example, there is no number larger than the largest number and that makes the COUNTIF function return 0 (zero).
The COUNTIF function also counts text values, this is demonstrated in the next section below.
How to rank text
Did you know that it is possible to rank text strings using the COUNTIF function? It returns a number corresponding to the position in a sorted list except that the top text value starts with 0 (zero).
For example:
A - 0
B - 1
C - 2
D - 3
F - 4
The list above is sorted from A to Z.
Formula in cell C3:
The COUNTIF function is one of the more useful functions in Excel, this is just one example.
Recommended articles
Counts the number of cells that meet a specific condition.
'RANK' function examples
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
Table of Contents How to rank text uniquely without duplicates How to rank uniquely based on a condition Get Excel […]
Functions in 'Compatibility' category
The RANK function function is one of 21 functions in the 'Compatibility' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form