Documentation/Calc Functions/PERCENTRANK

    From The Document Foundation Wiki

    Function name:

    PERCENTRANK

    Category:

    Statistical Analysis

    Summary:

    Calculates the percentage rank of a value of interest with respect to an unordered set of numbers, using a percentage rank definition that is inclusive of the smallest and largest values in the dataset. PERCENTRANK returns a percentage, expressed as a number in the range [0.0, 1.0], that can be used to understand the relative standing of a value compared to the numbers in a dataset.

    PERCENTRANK returns 0.0 when the value of interest is equal to the smallest number in the dataset and returns 1.0 when the value is equal to the largest number in the dataset. Percentage ranks between 0.0 and 1.0 represent relative positions between the smallest and largest numbers in the dataset.

    Note that the value of interest passed to PERCENTRANK need not match any number that exists in the supplied dataset, although it must lie within the range encompassed by the smallest and largest numbers of the dataset. If the value is not equal to a number in the dataset, the function interpolates to calculate the required percentage rank.

    Syntax:

    PERCENTRANK(Data; Value[; Significance])

    Returns:

    Returns a real number in the range [0.0, 1.0] with a specified number of significant digits, which is the percentage rank of the specified value with respect to the supplied dataset.

    Arguments:

    Data is an array of real numbers. This argument can take the form of an explicit cell range, the name of a named range, the name of a database range, or an inline constant array. The numbers in Data need not be in any specific order and individual values can appear more than once.

    Value is a real number, or a reference to a cell containing that number, which is the value whose percentage rank is to be determined. Value must not be less than the smallest number in Data and must not be greater than the largest number in Data.

    Significance is a positive integer, or a reference to a cell containing that number, which defines the number of significant digits to be used for the returned percentage rank (with rounding applied where needed). If omitted, the default value of 3 is used.

    • Text in cells and empty cells within Data are ignored.
    • If Data contains no numbers, then PERCENTRANK reports a #VALUE! error.
    • If Data contains only one number and the Value argument is not equal to that number, then PERCENTRANK reports a #VALUE! error.
    • If either Value or Significance is non-numeric, then PERCENTRANK reports a #VALUE! error.
    • If Value is less than the smallest value in Data or greater than the largest value in Data, then PERCENTRANK reports a #VALUE! error.
    • If Significance is a non-integer value, then PERCENTRANK truncates it to an integer value.
    • If Significance is less than 1, then PERCENTRANK reports an invalid argument error (Err:502).

    Additional details:

    • Calc's PERCENTRANK and PERCENTRANK.INC functions perform identical calculations. The requirements for PERCENTRANK are specified in ODF 1.2; PERCENTRANK.INC is provided for interoperability with Microsoft Excel.
    • Calc's related PERCENTRANK.EXC function implements a percentage rank definition that is exclusive of the smallest and largest values in the array.
    • For more information about percentage ranks, visit Wikipedia's Percentile rank page.

    Examples:

    Worked example

    The following example illustrates the principal features of the PERCENTRANK function.

    • Cells A2:A6 contain a small dataset comprising five numbers. The values used in this example are intentionally simplistic and are already ordered for convenience. PERCENTRANK will produce the same results whichever cell order is selected for the numbers.
    • Cells C2:C8 contain selected values for the Value argument.
    • Cell D2 contains the formula =PERCENTRANK($A$2:$A$6; $C2); cell D3 contains the formula =PERCENTRANK($A$2:$A$6; $C3); and so on for cells D4 to D8.
      A B C D
    1 Data Value PERCENTRANK
    2 1 0 #VALUE!
    3 2 1 0
    4 3 2 0.25
    5 4 3 0.5
    6 5 4 0.75
    7 5 1
    8 6 #VALUE!

    The important points to note are:

    • A Value of 0 is less than the smallest number in Data and so PERCENTRANK returns the #VALUE! error.
    • There are no numbers in Data that are smaller than 1 and so PERCENTRANK returns 0 when Value is set to 1.
    • Data contains no duplicate numbers or numbers greater than 5 and so PERCENTRANK returns 1 when Value is set to 5. Note that if the value in cell A5 was changed to 5, then the contents of cells D6 and D7 would change to 0.625 and 0.75 respectively.
    • A Value of 6 is greater than the largest number in Data and so PERCENTRANK returns the #VALUE! error.

    Other examples

    Formula Description Returns
    =PERCENTRANK(A1:A5; 7; 4) where cells A1:A5 contain the numbers 5, 7, 3, 2, and 9 respectively. Here the function calculates the percentage rank of the value 7 in the specified dataset, with the result returned to four significant digits. 0.75
    =PERCENTRANK(A1:A5; 6; 2) where cells A1:A5 contain the same values as in the previous example. Here the function calculates the percentage rank of the value 6 in the specified dataset, with the result returned to two significant digits. Note that the returned value is rounded – for example, the formula =PERCENTRANK(A1:A5; 6; 3) returns 0.625. 0.63

    Related LibreOffice functions:

    PERCENTILE

    PERCENTILE.EXC

    PERCENTILE.INC

    PERCENTRANK.EXC

    PERCENTRANK.INC

    RANK

    ODF standard:

    Section 6.18.58, part 2

    Related (or similar) Excel functions:

    PERCENTRANK