Hello all and welcome back to the Excel Tip of the Week. This week, we have a Creator level post in which we are taking a second look at the useful ranking functions, RANK.AVG and RANK.EQ. These were first discussed back in TOTW #145.
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
Excel community
This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.
- Excel Tips and Tricks #496 – ‘Check Performance’ in Excel
- Excel Tips and Tricks #495 - Excel “Tick”ery!
- Excel Tips & Tricks #494 - How to add a custom ribbon to your workbook part 3
- Excel Tips & Tricks #493 - How to add a custom ribbon to your workbook part 2
- Excel Tips & Tricks #492 - How to add a custom ribbon to your workbook
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.
