Syntax
RANK.AVG(number, ref, [order])
| Parameter | Description |
|---|---|
| number | Parameter of the RANK.AVG function. |
| ref | Parameter of the RANK.AVG function. |
| [order] | (Optional.) Parameter of the RANK.AVG function. |
Examples
Average rank with ties
Formula
=RANK.AVG(B5, $B$2:$B$20)
If the value in B5 ties with another value for positions 3 and 4, both receive rank 3.5 instead of both getting 3.
Ascending average rank
Formula
=RANK.AVG(C3, $C$2:$C$50, 1)
Ranks from smallest to largest, averaging tied ranks. Useful for ranking times where lower is better.
Fair competition ranking
Formula
=RANK.AVG(D8, $D$2:$D$100)
Two athletes with the same score both receive rank 2.5 instead of both getting rank 2, fairly splitting positions 2 and 3.
Common Errors
#N/A
The number to rank is not found in the reference range.
#VALUE!
A non-numeric argument was supplied.
Tips
Fractional ranks are normal
RANK.AVG intentionally produces non-integer ranks (like 3.5) for ties. This is correct behavior, not an error.
When to use AVG vs. EQ
Use RANK.AVG for statistical analyses, Spearman correlations, or fair scoring. Use RANK.EQ for leaderboards where you want clean integer ranks.
Sum of all ranks
RANK.AVG preserves the property that all ranks sum to n*(n+1)/2, which is important for non-parametric statistical tests.
Try RANK.AVG in Viztab
Import your data and use RANK.AVG with 370+ other formulas. No signup required.
Open Viztab