Rank.AVG() and how it's different from Rank() and Rank.EQ() in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

We’ve talked, at length about Excel’s Rank()/Rank.EQ functions and coping with joint or equal rankings. What about Rank.AVG()?

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().

`RANK.AVG(number,ref,[order])`

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().

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy