Documentation/Calc Functions/PERCENTILE.EXC

    From The Document Foundation Wiki
    This page contains changes which are not marked for translation.

    Function name:

    PERCENTILE.EXC

    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.EXC implements an exclusive percentile definition and returns a value that is greater than k% of the numbers in the dataset, while (100-k)% of the numbers in the dataset are greater than or equal to the value returned.

    Note that the value returned by PERCENTILE.EXC 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.EXC(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 a 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.EXC reports a #VALUE! error.
    • If Data contains exactly one number, then PERCENTILE.EXC returns that value if (and only if) Alpha is equal to 0.5.
    • If Alpha is non-numeric, then PERCENTILE.EXC reports a #VALUE! error.
    • If Alpha is less than or equal to 0.0, then PERCENTILE.EXC reports an invalid argument error (Err:502).
    • If Alpha is greater than 0.0 but less than [math]\displaystyle{ \tfrac{1}{n+1} }[/math], where n is the count of numbers in Data, then PERCENTILE.EXC reports a parameter list error (Err:504).
    • If Alpha is greater than [math]\displaystyle{ \tfrac{n}{n+1} }[/math] but less than 1.0, then PERCENTILE.EXC reports a parameter list error (Err:504).
    • If Alpha is greater than or equal to 1.0, then PERCENTILE.EXC reports an invalid argument error (Err:502).
    • If Alpha satisfies the above conditions and is a multiple of [math]\displaystyle{ \tfrac{1}{n+1} }[/math], then PERCENTILE.EXC returns one of the numbers in the dataset.
    • If Alpha satisfies the above conditions but is not a multiple of [math]\displaystyle{ \tfrac{1}{n+1} }[/math], then PERCENTILE.EXC returns an interpolated value.

    Additional details:

    • Calc's related PERCENTILE and PERCENTILE.INC functions implement an inclusive percentile definition. For these functions, the Alpha argument lies in the range [0, 1].
    • The following are common settings for the Alpha argument:
    • Alpha = 0.25 returns the first quartile.
    • Alpha = 0.5 returns the median (or second quartile).
    • Alpha = 0.75 returns the third quartile.
    • For more information about percentiles, visit Wikipedia's Percentile page.

    Examples:

    Worked example

    The following example illustrates the principal features of the PERCENTILE.EXC 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.EXC will produce the same results whichever cell order is selected for the numbers.
    • Cells C2:C16 contain selected values for the Alpha argument.
    • Cell D2 contains the formula =PERCENTILE.EXC($A$2:$A$6; $C2); cell D3 contains the formula =PERCENTILE.EXC($A$2:$A$6; $C3); and so on for cells D4 to D16.
      A B C D
    1 Data Alpha PERCENTILE.EXC
    2 1 0.0 Err:502
    3 2 0.1 Err:504
    4 3 0.166666666666667 1
    5 4 0.2 1.2
    6 5 0.3 1.8
    7 0.333333333333333 2
    8 0.4 2.4
    9 0.5 3
    10 0.6 3.6
    11 0.666666666666667 4
    12 0.7 4.2
    13 0.8 4.8
    14 0.833333333333333 5
    15 0.9 Err:504
    16 1.0 Err:502

    The important points to note are:

    • Alpha values equal to 0.0 or 1.0 are not supported and result in an invalid argument error (Err:502).
    • Alpha values in the range 0.0 < Alpha < [math]\displaystyle{ \tfrac{1}{6} }[/math] or in the range [math]\displaystyle{ \tfrac{5}{6} }[/math] < Alpha < 1.0 are not supported and result in a parameter list error (Err:504).
    • With five numbers in the dataset, valid values of Alpha that are multiples of [math]\displaystyle{ \tfrac{1}{6} }[/math] (that is [math]\displaystyle{ \tfrac{1}{6} }[/math], [math]\displaystyle{ \tfrac{2}{6} }[/math], [math]\displaystyle{ \tfrac{3}{6} }[/math], [math]\displaystyle{ \tfrac{4}{6} }[/math], and [math]\displaystyle{ \tfrac{5}{6} }[/math]) return percentile values that are in the original dataset.
    • With five numbers in the dataset, valid values of Alpha that are not multiples of [math]\displaystyle{ \tfrac{1}{6} }[/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.EXC(A1:A5; 20%) where cells A1:A5 contain the numbers 5, 7, 3, 2, and 9 respectively. Here the function calculates the 20th 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.166666666666667. 2.2
    =PERCENTILE.EXC({5; 7; 3; 2; 9}; 5/6) Here the function calculates the 83.3333333333333th 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.166666666666667, and so PERCENTILE.EXC returns an exact value from the dataset. 9

    Related LibreOffice functions:

    MAX

    MEDIAN

    MIN

    PERCENTILE

    PERCENTILE.INC

    PERCENTRANK

    PERCENTRANK.EXC

    PERCENTRANK.INC

    QUARTILE

    RANK

    ODF standard:

    None

    Related (or similar) Excel functions:

    PERCENTILE.EXC since v.2010.