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


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


Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.