Rank.AVG() and how it’s different from Rank() and Rank.EQ() in Excel
Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.
Rank.AVG() works differently for equal rankings because it returns an average of the combined rankings.
For example, joint 3rd place getters will return the value ‘3.5’ by Rank.AVG() which is the average of ranks 3 and 4 (3 plus 4 divided by 2 ). Rank() and Rank.EQ() return ‘3’.
The basic format of Rank.AVG() is the same as Rank.EQ() or Rank().
Number – the number whose rank you want to find. Usually an adjacent cell in a nearby column.
Ref – the range of values to rank within. Can be a cell range, named range or table range. Non-numeric values in Ref are ignored. Usually a nearby column of cells.
Order – Optional. 0 = descending, the default. 1 = ascending.
Note: the official Microsoft page for Rank.AVG() gives a pointless example that’s no help in explaining the function. See below.
Rank.EQ() or Rank.AVG()?
Which one you use depends on the exact rules you or your organization needs. Either function will work with our suggestion for handling joint and equal rankings.
Misinformation from Microsoft
Here’s the official Microsoft example for Rank.AVG() at mid-January 2018. Since all the temperatures have different values, there’s no difference in the results between Rank.AVG() and Rank.EQ()! The example works, but doesn’t show the crucial difference with Rank.AVG().
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.