Excel Tip of the Week #383 - Revisiting ranking functions

What do these functions do?

Both of these functions provide a number’s rank position in a given list. You can have them either count rank in ascending or descending order as appropriate. So, for example, you could take sales figures for several customers and then use a ranking function to determine the rank position of each:

The functions work identically except for how they handle duplicated values. The names are helpful here – RANK.EQ will return an equal result for every tied value (equal to the smallest rank covered by the tie), and RANK.AVG will return the average of all the ranks covered by the tie. Each can be appropriate depending on what you’re trying to convey.

It’s worth noting that older versions of Excel had only a single function, RANK, which was equivalent to the current RANK.EQ function. While this still works and is available in later versions for compatibility reasons, for avoidance of confusion you should always use one of the two newer and more specific functions.

How do you write these functions?

Both functions share the same syntax:

=RANK.EQ(number to be compared, list to compare against, order)

=RANK.AVG(number to be compared, list to compare against, order)

Number to be compared is the number being ranked.

List to compare against is the full range of numbers being ranked.

Order determines if the order is descending or ascending – 0 for descending (largest item ranked #1), or 1 for ascending (smallest item ranked #1).

In any case these functions will return an #N/A! error if the value being compared does not match any values in the list; for that reason these functions are almost always written comparing a number that is contained inside the list being ranked. Here’s an example:

You can see this example yourself in the attached file. Note that the RANK.EQ and RANK columns are completely identical, and also compare how the different functions treat the four identical values highlighted in orange. RANK.AVG averages the positions of 6, 7, 8, and 9 for a return of 7.5, whereas RANK.EQ just uses the smallest rank of 6.

Finally, it’s worth noting that RANK.EQ (and therefore the older RANK function) are equivalent to this COUNTIF function:

=COUNTIF(list to compare to, “>”&value to compare)+1

You may also like

• Previous post - Keeping your VBA code readable
• TOTW index
• Next time – Data types!