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
Invalid email address
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’.

rank avg and how its different from rank and rank eq in excel 16378 - Rank.AVG() and how it's different from Rank() and Rank.EQ() in Excel

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

rank avg and how its different from rank and rank eq in excel microsoft excel 16380 - Rank.AVG() and how it's different from Rank() and Rank.EQ() in Excel

subs profile e1563205311409 - Rank.AVG() and how it's different from Rank() and Rank.EQ() in Excel
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
Invalid email address