Excel functions / Statistics
RANK.AVG function
The position of a number in a list. Tied values share the average of their positions.
Syntax
=RANK.AVG(number, ref, [order])
Arguments
number: The value to rank.ref: The list of numbers.order(optional): 0 or omitted = largest first, 1 = smallest first.
When to use it
RANK.AVG gives a value's rank in a list, like RANK, but values that tie share the average of the places they cover: two tied for second both get 2.5. Statistical tests that work on ranks use this method.
Examples
A tie for second place
A B C D 1 Name Score 2 Ana 90 2.5 3 Ben 85 4 Chloe 85 5 Dev 70 In D2:
=RANK.AVG(B3, B2:B5)→ 2.5RANK gives both the higher place
A B C D 1 Name Score 2 Ana 90 0.5 3 Ben 85 4 Chloe 85 5 Dev 70 In D2:
=RANK.AVG(B3, B2:B5)-RANK(B3, B2:B5)→ 0.5
Common mistakes
- Direction. By default the largest value ranks 1. Give 1 as the third argument to rank the smallest first.
RANK.AVG in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: RANK.AVG
- Danish: PLADS.GNSN
- Dutch: RANG.GEMIDDELDE
- Finnish: ARVON.MUKAAN.KESKIARVO
- French: MOYENNE.RANG
- German: RANG.MITTELW
- Hungarian: RANG.ÁTL
- Italian: RANGO.MEDIA
- Norwegian: RANG.GJSN
- Polish: POZYCJA.ŚR
- Portuguese (Brazil): ORDEM.MÉD
- Portuguese (Portugal): ORDEM.MÉD
- Russian: РАНГ.СР
- Spanish: JERARQUIA.MEDIA
- Swedish: RANG.MED
- Turkish: RANK.ORT
Try RANK.AVG in the playground
Related functions
- RANK: The position of a number in a list (1 = largest by default).
- PERCENTRANK.INC: Where a value stands in a list, from 0 (the lowest) to 1 (the highest).
- LARGE: The k-th largest value (k = 1 is the maximum).
- SMALL: The k-th smallest value (k = 1 is the minimum).