Documentation/Calc Functions/QUARTILE.INC

    From The Document Foundation Wiki

    Function name:

    QUARTILE.INC

    Category:

    Statistical Analysis

    Summary:

    Calculates the minimum, the maximum, or one of the three quartiles of an unordered set of numbers. These five 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.INC implements an inclusive quartile definition and returns a value that is greater than or equal to the appropriate percentage of the numbers in the dataset.

    Note that the value returned by QUARTILE.INC 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.INC(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 [0, 4], or a reference to the cell containing that number, which specifies the type of quartile to be calculated. These codes have the following meanings:

    • 0 corresponds to the minimum of the dataset
    • 1 corresponds to the first quartile
    • 2 corresponds to the second quartile or median
    • 3 corresponds to the third quartile
    • 4 corresponds to the maximum of the dataset

    The following conditions and errors may arise:

    • Text in cells and empty cells within Data are ignored.
    • If Data contains no numbers, then QUARTILE.INC reports a #VALUE! error.
    • If Data contains exactly one number, then QUARTILE.INC returns that value for any valid value of Type.
    • If Type is non-numeric, then QUARTILE.INC reports a #VALUE! error.
    • If Type is a non-integer value, then QUARTILE.INC truncates it to an integer.
    • If Type is less than 0 or greater than 4, then QUARTILE.INC reports an invalid argument error (Err:502).

    Additional details:

    • Calc's QUARTILE and QUARTILE.INC functions perform identical calculations. The requirements for QUARTILE are specified in ODF 1.2; QUARTILE.INC is provided for interoperability with Microsoft Excel.
    • Calc's related QUARTILE.EXC function implements an exclusive quartile definition.
    • If Type is equal to 2, then QUARTILE.INC uses the following relationship:
    [math]\displaystyle{ \text{QUARTILE.INC}(Data;\:2)\:=\:\text{MEDIAN}(Data) }[/math]
    • For other integer values of Type, QUARTILE.INC uses the following relationship:
    [math]\displaystyle{ \text{QUARTILE.INC}(Data;\:Type)\:=\:\text{PERCENTILE}(Data; 0.25\times Type) }[/math]
    • The name space for QUARTILE.INC is COM.MICROSOFT.QUARTILE.INC.
    • For more information on quartiles, visit Wikipedia's Quartile page.

    Examples:

    Formula Description Returns
    =QUARTILE.INC(A1:A5; 0) where cells A1:A5 contain the numbers 5, 7, 3, 2, and 9 respectively. Here the function returns the minimum value in the dataset. 2
    =QUARTILE.INC(A1:A5; 1) where cells A1:A5 contain the same values as in the previous example. Here the function calculates the first quartile of the dataset. 3
    =QUARTILE.INC(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.INC(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. 7
    =QUARTILE.INC(A1:A5; 4) where cells A1:A5 contain the same values as in the previous example. Here the function returns the maximum value in the dataset. Note that the formula =QUARTILE.INC(A1:A5; 4.9) returns the same value because QUARTILE.INC truncates the non-integer value of the Type argument. 9
    =QUARTILE.INC({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.INC

    PERCENTRANK.INC

    QUARTILE

    QUARTILE.EXC

    RANK

    ODF standard:

    None

    Related (or similar) Excel functions:

    QUARTILE.INC