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

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

  1. A tie for second place

    ABCD
    1NameScore
    2Ana902.5
    3Ben85
    4Chloe85
    5Dev70

    In D2: =RANK.AVG(B3, B2:B5) → 2.5

    Try this example

  2. RANK gives both the higher place

    ABCD
    1NameScore
    2Ana900.5
    3Ben85
    4Chloe85
    5Dev70

    In D2: =RANK.AVG(B3, B2:B5)-RANK(B3, B2:B5) → 0.5

    Try this example

Common mistakes

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.

Translate a whole formula

Try RANK.AVG in the playground

Related functions