Documentation/Calc Functions/RANK.AVG

    From The Document Foundation Wiki

    Function name:

    RANK.AVG

    Category:

    Statistical Analysis

    Summary:

    Calculates the rank of a given value within a supplied list of numbers. If you sort the list, a number's rank is its position within the sorted list.

    If there are repeated numeric values in the list, the average rank for the same value is returned.

    Syntax:

    RANK.AVG(Value; Data[; Type])

    Returns:

    Returns a positive numeric value which is the rank of the Value in the given Data with given sequence order Type. It ranges from 1 to the number of elements in Data.

    If a number in Data occurs more than once, it is given the same rank, but it also increments the rank for subsequent different numbers.

    If Value does not exist in the range of Data, then the function returns an error.

    If Value is not a real number (nor a reference to a real number), then the function returns an error.


    Note pin.svg

    Note:
    The difference between RANK.AVG and RANK.EQ/RANK occurs when there are repeated numeric values in Data. The RANK.EQ/RANK function returns the same "closer-to-first" rank, whereas the RANK.AVG function returns the average rank for all the same repeated values. This means that RANK.EQ/RANK returns positive integer numbers for repeated numeric values in Data, whereas RANK.AVG may return positive non-integer numbers for such repeated numeric values in Data.

    Arguments:

    Value is a real number, or a reference to a cell containing that number, which is the value whose rank is to be determined.

    Data is an array, or a reference to a range of cells, containing the list of real numbers from which Value's rank is to be determined.

    Type is the (optional) sequence order. The intended values for Type are as follows:

    • Type = 0 (zero, default). Data is ranked in Descending order; from the greatest item of the array to the smallest. The largest/greatest value in Data gets the first rank (1). The smallest/least value in Data gets the last (greatest number) rank.
    • Type = 1. Data is ranked in Ascending order; from the smallest/least item of the array to the greatest. The smallest value in Data gets the first rank (1). The largest/greatest value in Data gets the last (greatest number) rank.

    If a number in Data occurs more than once, it is given the average rank of all the same numbers.

    Additional details:

    If Data includes values that are not real numbers (e.g. "string"), then the function ignores such values.

    If Type is either TRUE or any non-zero real number, then Type is treated as 1.

    If Type is FALSE, then Type is treated as 0 (zero).

    • RANK.AVG is available since LibreOffice 4.3. Take care therefore if you need compatibility.
    • This function is not part of the Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula (OpenFormula) Format standard. The name space is COM.MICROSOFT.RANK.AVG. Take care therefore if you need compatibility.
    Warning.svg

    Warning:
    Since LibreOffice 4.3 and until 7.5.2 (included), the function had an implementation error. For numeric values that are not listed within Data, the function returned numeric values, which was incorrect. Since LibreOffice 7.5.3, the implementation is correct, not only for values listed within Data. The "Examples" section includes results for the correct implementation, with a mark for cases in which an incorrect result would be obtained with the prior incorrect implementation.


    Examples:

    See: RANK#Examples:

    Related LibreOffice functions:

    COUNT

    COUNTIF

    COUNTIFS

    LARGE

    MAX

    MIN

    RANK

    RANK.EQ

    SMALL

    SUMPRODUCT

    ODF standard:

    None. See instead COM.MICROSOFT.RANK.AVG

    Related (or similar) Excel functions:

    RANK.AVG since v.2010.