Table of Contents
Often you may want to rank numbers in Excel and force each number to have a unique rank.
You can use the following formulas to do so:
Method 1: Rank Numbers Uniquely in Ascending Order
=RANK(B2,$B$2:$B$11)+COUNTIF(B$2:B2,B2)-1
Method 2: Rank Numbers Uniquely in Descending Order
=RANK(B2,$B$2:$B$11,1)+COUNTIF(B$2:B2,B2)-1
Both formulas will rank the numbers uniquely in the range B2:B11.
If two numbers are the same, then the number that appears first will receive the lower rank.
The following example shows how to use each method in practice.
Example: How to Rank Numbers Uniquely in Excel
Suppose we have the following dataset in Excel that shows the exam scores received by various students in some class:
Suppose we would like to rank the exam scores of each student with ranks ranging between 1 and 10.
We can type the following formulas into cells C2, D2 and E2 to apply different ranking methods:
- C2 (Non-Unique Rank):
- =RANK(B2,$B$2:$B$11)
- D2 (Rank Uniquely in Ascending Order):
- =RANK(B2,$B$2:$B$11)+COUNTIF(B$2:B2,B2)-1
- E2 (Rank Uniquely in Descending Order):
- =RANK(B2,$B$2:$B$11,1)+COUNTIF(B$2:B2,B2)-1
We can then click and drag these formulas down to each remaining cell in each column:
Here is how each ranking method handled this tie:
Method 1: Non-Unique Rank
This method simply assigned a rank of 1 to both of these highest values.
Method 2: Rank Unique Ascending
This method assigned a rank of 1 to the highest value.
If two values were tied, it simply assigned a lower rank to the value that appeared first.
Since Doug appeared first in the dataset he received a rank of 1 and Eric then received a rank of 2.
Method 3: Rank Unique Descending
This method assigned a rank of 1 to the lowest value.
If two values were tied, it simply assigned a lower rank to the value that appeared first.
Since Doug appeared first in the dataset he received a rank of 9 and Eric then received a rank of 10.