Documentation/Calc Functions/PERCENTILE

    From The Document Foundation Wiki

    Function name:

    PERCENTILE

    Category:

    Statistical Analysis

    Summary:

    Calculates the kth percentile (0 ≤ k ≤ 100) of an unordered set of numbers. Percentiles can be used to understand the significance of particular values within a dataset.

    PERCENTILE implements an inclusive percentile definition and returns a value that is greater than or equal to k% of the numbers in the dataset, while (100-k)% of the numbers in the dataset are greater than the value returned.

    Note that the value returned by PERCENTILE is not necessarily a number that exists in the supplied dataset because the function interpolates between numbers to calculate the required percentile where necessary.

    Syntax:

    PERCENTILE(Data; Alpha)

    Returns:

    Returns a real number, which is the specified percentile value for the supplied data set.

    Arguments:

    Data is an array of real numbers containing the data values for which a percentile is required. 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.

    Alpha is a real number in the range [0, 1] (expressed as a percentage or a decimal fraction), or a reference to the cell containing that number, which specifies the percentile to be calculated.

    • Text in cells and empty cells within Data are ignored.
    • If Data contains no numbers, then PERCENTILE reports a #VALUE! error.
    • If Data contains exactly one number, then PERCENTILE returns that value for any valid value of Alpha.
    • If Alpha is non-numeric, then PERCENTILE reports a #VALUE! error.
    • If Alpha is less than 0.0 or greater than 1.0, then PERCENTILE reports an invalid argument error (Err:502).
    • If Alpha is a multiple of [math]\displaystyle{ \tfrac{1}{n-1} }[/math], where n is the count of numbers in Data, then PERCENTILE returns one of the numbers in the dataset.
    • If Alpha is not a multiple of [math]\displaystyle{ \tfrac{1}{n-1} }[/math], then PERCENTILE returns an interpolated value.

    Additional details:

    • Calc's PERCENTILE and PERCENTILE.INC functions perform identical calculations. The requirements for PERCENTILE are specified in ODF 1.2; PERCENTILE.INC is provided for interoperability with Microsoft Excel.
    • Calc's related PERCENTILE.EXC function implements an exclusive percentile definition. For PERCENTILE.EXC, the Alpha argument cannot be less than [math]\displaystyle{ \tfrac{1}{n+1} }[/math] or greater than [math]\displaystyle{ \tfrac{n}{n+1} }[/math], where n is the count of numbers found Data.
    • The following are common settings for the Alpha argument:
    • Alpha = 0.0 returns the minimum.
    • Alpha = 0.25 returns the first quartile.
    • Alpha = 0.5 returns the median (or second quartile).
    • Alpha = 0.75 returns the third quartile.
    • Alpha = 1.0 returns the maximum.
    • For more information about percentiles, visit Wikipedia's Percentile page.

    Examples:

    Worked example

    The following example illustrates the principal features of the PERCENTILE 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. PERCENTILE will produce the same results whichever cell order is selected for the numbers.
    • Cells C2:C14 contain selected values for the Alpha argument.
    • Cell D2 contains the formula =PERCENTILE($A$2:$A$6; $C2); cell D3 contains the formula =PERCENTILE($A$2:$A$6; $C3); and so on for cells D4 to D14.
      A B C D
    1 Data Alpha PERCENTILE
    2 1 0.0 1
    3 2 0.1 1.4
    4 3 0.2 1.8
    5 4 0.25 2
    6 5 0.3 2.2
    7 0.4 2.6
    8 0.5 3
    9 0.6 3.4
    10 0.7 3.8
    11 0.75 4
    12 0.8 4.2
    13 0.9 4.6
    14 1.0 5

    The important points to note are:

    • With five numbers in the dataset, then values of Alpha that are multiples of [math]\displaystyle{ \tfrac{1}{4} }[/math] (that is 0, 0.25, 0.5, 0.75, and 1) return percentile values that are in the original dataset.
    • With five numbers in the dataset, then values of Alpha that are not multiples of [math]\displaystyle{ \tfrac{1}{4} }[/math] return percentile values that are not in the original dataset. These values are interpolated between two of the values from the original dataset.

    Other examples

    Formula Description Returns
    =PERCENTILE(A1:A5; 10%) where cells A1:A5 contain the numbers 5, 7, 3, 2, and 9 respectively. Here the function calculates the 10th percentile of the dataset in cells A1:A5. In this case, an interpolated value is returned because the Alpha argument is not a multiple of [math]\displaystyle{ \tfrac{1}{n-1} }[/math] or 0.25. 2.74
    =PERCENTILE({5; 7; 3; 2; 9}; 25%) Here the function calculates the 25th percentile of the dataset passed as an inline constant array. In this case no interpolation is needed since Alpha is a multiple of [math]\displaystyle{ \tfrac{1}{n-1} }[/math] or 0.25, and so PERCENTILE returns an exact value from the dataset. 3

    Related LibreOffice functions:

    MAX

    MEDIAN

    MIN

    PERCENTILE.EXC

    PERCENTILE.INC

    PERCENTRANK

    PERCENTRANK.EXC

    PERCENTRANK.INC

    QUARTILE

    RANK

    ODF standard:

    Section 6.18.57, part 2

    Related (or similar) Excel functions:

    PERCENTILE