RANK.AVG Function

Returns a number's rank in a list; ties get the average rank.

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A number

Want to practice RANK.AVG in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

RANK.AVG works like RANK.EQ, except tied values get the average of the ranks they cover. Two values tied for 5th and 6th both get 5.5.

That's the convention statistical tests (such as rank correlation) expect.

Syntax

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

Arguments

ArgumentRequired?What it does
numberYesThe value to rank.
refYesThe list to rank it in. Lock it with $ when filling down.
orderNo0 or left out = largest is 1; 1 = smallest is 1.

Examples

Example 1 — Sales ranking with averaged ties

Compare with RANK.EQ, which gives Ben and Gus 5.

D2fx=RANK.AVG(C2,$C$2:$C$8)
ABCD
1RepRegionSalesRank
2AnaNorth620002
3BenSouth485005.5
4CyNorth500004
5DiEast712501
6EdSouth398007
7FayNorth554003
8GusEast485005.5
=RANK.AVG(C2,$C$2:$C$8) Explain

Common mistakes

  • #N/A The number isn't in ref.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.