Documentation/Calc Functions/QUARTILE.EXC

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

    Function name:

    QUARTILE.EXC

    Category:

    Statistical Analysis

    Summary:

    Calculates one of the three quartiles of an unordered set of numbers. These values provide a statistical summary of the dataset, giving information about its center and spread.

    The three quartiles split the ordered dataset into four intervals:

    • First quartile (Q1) - 25% of the data are below this value.
    • Second quartile (Q2) - splits the data in half. Also known as the median.
    • Third quartile (Q3) - 25% of the data are above this value.

    QUARTILE.EXC implements an exclusive quartile definition and returns a value that is greater than the appropriate percentage of the numbers in the dataset.

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

    Syntax:

    QUARTILE.EXC(Data; Type)

    Returns:

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

    Arguments:

    Data is an array of real numbers containing the dataset for which a quartile 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.

    Type is an integer in the range [1, 3], or a reference to the cell containing that number, which specifies the type of quartile to be calculated. These codes have the following meanings:

    • 1 corresponds to the first quartile
    • 2 corresponds to the second quartile or median
    • 3 corresponds to the third quartile

    The following conditions and errors may arise:

    • Text in cells and empty cells within Data are ignored.
    • If Data contains no numbers, then QUARTILE.EXC reports a #VALUE! error.
    • If Data contains exactly one number, then QUARTILE.EXC returns that value if (and only if) Type equals 2.
    • If Type is non-numeric, then QUARTILE.EXC reports a #VALUE! error.
    • If Type is a non-integer value, then QUARTILE.EXC truncates it to an integer.
    • If Type is less than 1 or greater than 3, then QUARTILE.EXC reports an invalid argument error (Err:502).

    Additional details:

    • Calc's QUARTILE and QUARTILE.INC functions implement an inclusive quartile definition. For these functions, the Type argument is an integer in the range [0, 4].
    • If Type is equal to 2, then QUARTILE.EXC uses the following relationship:
    [math]\displaystyle{ \text{QUARTILE.EXC}(Data;\:2)\:=\:\text{MEDIAN}(Data) }[/math]
    • For other integer values of Type, QUARTILE.EXC uses the following relationship:
    [math]\displaystyle{ \text{QUARTILE.EXC}(Data;\:Type)\:=\:\text{PERCENTILE.EXC}(Data; 0.25\times Type) }[/math]
    • The name space for QUARTILE.EXC is COM.MICROSOFT.QUARTILE.EXC.
    • For more information on quartiles, visit Wikipedia's Quartile page.

    Examples:

    Formula Description Returns
    =QUARTILE.EXC(A1:A5; 1) where cells A1:A5 contain the numbers 5, 7, 3, 2, and 9 respectively. Here the function calculates the first quartile of the dataset. 2.5
    =QUARTILE.EXC(A1:A5; 2) where cells A1:A5 contain the same values as in the previous example. Here the function calculates the second quartile or median of the dataset. 5
    =QUARTILE.EXC(A1:A5; 3) where cells A1:A5 contain the same values as in the previous example. Here the function calculates the third quartile of the dataset. Note that the formula =QUARTILE.EXC(A1:A5; 3.9) returns the same value because QUARTILE.EXC truncates the non-integer value of the Type argument. 8
    =QUARTILE.EXC({5, 7, 3, 2, 8, 9}; 2) Here the function calculates the second quartile or median of the dataset. Note that with an even number of entries in the dataset, the median is calculated by interpolation and is not an entry in the dataset. 6

    Related LibreOffice functions:

    MAX

    MEDIAN

    MIN

    PERCENTILE.EXC

    PERCENTRANK.EXC

    QUARTILE

    QUARTILE.INC

    RANK

    ODF standard:

    None

    Related (or similar) Excel functions:

    QUARTILE.EXC since v.2010.