Excel has three functions that can rank values. Unfortunately none of them provide a way to create a sequential list of ranking numbers when there are duplicates. Luckily we can adapt the RANK function to handle duplicates and create a sequential list of ranking numbers.
In the image below I have used Excel’s three ranking functions.
When there are duplicates none of the functions provide a sequential list of numbers that provide a complete ranking for the list.
The RANK and RANK.EQ duplicate the lowest ranking number and omit numbers. In this case 2 is listed twice but there is no 3.
The RANK.AVG averages the ranking numbers and displays the average. So the ranks 2 and 3 are averaged to 2.5 and are shown twice
To get a sequential list of ranking numbers we can modify the RANK function.
In the image below I have added a COUNTIF function to the RANK function to provide the sequential ranking numbers.
Note the COUNTIF range expands as it is copied down as the first cell reference is fixed with $ signs, and the second cell reference is relative.
You can use this sequential list of ranking numbers to extract a listing based on top five or top ten values when creating things like dashboards and reports.
This handles multiple duplicates – see image below.
The value 300 is repeated 4 times and the formula still works.