Quick Navigation

RANK.AVG Function

Excel 2010+

Summary

The RANK.AVG function determines a number's position within a dataset, returning the average rank when duplicate values exist. This makes it ideal for accurate percentile analysis and competition scoring where ties need fair representation.

Syntax

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

Parameters

Parameter	Type	Required	Description
number	`Number`	Yes	Value whose rank you want to determine in the reference list
ref	`Range/Array`	Yes	Array or cell range containing the numbers to compare against
order	`Number`	No	Optional: 0/omitted=descending rank, nonzero=ascending rank

Using the RANK.AVG Function

RANK.AVG excels in scenarios requiring precise position determination within datasets, particularly when handling ties. Use descending order (default) for leaderboards and performance rankings where higher values rank better, or ascending order for error margins and growth metrics.

Common RANK.AVG Examples

Temperature Ranking Example

=RANK.AVG(94,B2:B8)

Ranks 94°F within temperature list B2:B8 (89,88,92,101,94,97,95), returning 4 as it's the 4th hottest day.

Sales Competition Leaderboard

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

Ranks individual sales (A2) against team totals (B2:B10) in descending order for leaderboard positioning.

Ascending Order for Test Scores

=RANK.AVG(C2,$C$2:$C$20,1)

Ranks test score in ascending order where lower scores receive better (lower number) ranks.

Frequently Asked Questions

RANK.AVG assigns the average of tied positions. Three values tied for 2nd and 3rd get rank 2.5.

RANK.AVG averages tied ranks (Excel 2010+); legacy RANK uses last rank position.

Yes, RANK.AVG automatically ignores non-numeric values in the reference range.

Common Errors and Solutions

#N/A Error

Cause: Number not found in ref or ref contains only non-numeric values

Solution: Verify number exists in ref range and contains valid numbers

#VALUE! Error

Cause: Invalid order argument (text instead of number)

Solution: Use 0/omitted for descending, any number ≠0 for ascending

Notes

Default order=0 (descending: largest=1st)
Perfect for leaderboards, performance metrics, percentile calculations
Replaces legacy RANK function with proper tie handling
Non-numeric ref values automatically excluded

Compatibility

Available in: Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in: Excel 2007, Excel 2003, Earlier versions

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2010+