RANK.AVG
Returns the rank of a number in a list. If multiple values tie, the average rank is returned. Modern replacement for the legacy RANK function.
RANK.AVG(number, ref, [order])Arguments
numberThe number whose rank you want to find.refReference to the list of numbers.orderOptional. 0 or omitted = descending; non-zero = ascending.(optional)
=RANK.AVG(10,{10,10,20,30})Two 10s share ranks 3 and 4 → average 3.5
- •Available since Excel 2010
- •Use RANK.EQ if you want all tied values to share the same (highest) rank
- •Returns #N/A if number is not in ref
- •Expecting RANK.AVG to always return a whole number - when values tie, it returns the average of the ranks they would have occupied, which is often a decimal like 3.5
- •Forgetting that the order argument controls ascending versus descending ranking, and getting the default (descending) backward from what's actually needed for a particular dataset
- •Referencing a number that isn't actually present in ref, which returns a #N/A error rather than the nearest rank
RANK.EQGives all tied values the same, higher rank instead of averaging, a different tie-breaking approach than RANK.AVG.LARGEFinds the value at a specific rank position, roughly the inverse operation of what RANK.AVG determines.PERCENTRANKExpresses a value's rank as a percentage rather than a positional number, useful when a normalized rank matters more than the raw position.Why does RANK.AVG return a decimal like 3.5?
That happens when values tie - RANK.AVG averages the rank positions the tied values would occupy. Two values tied for 3rd and 4th place both get 3.5, rather than both getting 3 or both getting 4.
What does the order argument do in RANK.AVG?
It controls the ranking direction - 0 or omitted ranks in descending order (largest number gets rank 1), while any non-zero value ranks in ascending order (smallest number gets rank 1).
Why does RANK.AVG return a #N/A error?
The number you're looking up isn't actually present in the ref range - RANK.AVG needs an exact match, it won't find the closest value.
Need to translate a formula using RANK.AVG?
Use our translator to convert your complete formula