How to Rank Ties Fairly with RANK.AVG

RANK.AVG gives tied values the average of the positions they occupy — the fair, statistically correct way to rank ties.

You will learn to

• Rank with ties averaged using RANK.AVG
• Rank ascending with RANK.AVG
• Compare RANK.AVG with RANK.EQ

How it works

When two values tie, RANK.EQ gives both the higher rank (2 and 2) while RANK.AVG gives both the average of the places they occupy (2.5 and 2.5). The ranks then add up to the same total as untied ranks, which is why statistics such as Spearman’s rank correlation and the Mann-Whitney test require averaged ranks.

Use RANK.EQ for leaderboards people read; use RANK.AVG when the ranks feed into further calculations. Both take an optional third argument: 1 ranks the smallest value first.

=RANK.AVG(B3,$B$2:$B$10)