Documentation/Calc Functions/CHISQ.TEST

    From The Document Foundation Wiki

    Function name:

    CHISQ.TEST

    Category:

    Statistical Analysis

    Summary:

    Performs the calculations associated with a chi-square ([math]\displaystyle{ \chi }[/math]2) test, which is a statistical hypothesis test used to compare observed results with expected results.

    In outline, the CHISQ.TEST function:

    • Accepts two arguments specifying arrays with equal dimensions, one containing observed data values and the other containing the corresponding expected data values.
    • Uses a standard formula to calculate a chi-square statistic value from these data.
    • Calculates the degrees of freedom of the chi-square distribution that should be used to analyze the data.
    • Determines and returns the right-tail probability of the calculated chi-square statistic in the applicable chi-square distribution.

    Syntax:

    CHISQ.TEST(Data B; Data E)

    Returns:

    Returns a real number in the range [0, 1], which is a right-tail probability from the applicable chi-square distribution.

    Arguments:

    Data B is an array of real numbers containing the observed values. 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.

    Data E is an array of real numbers containing the expected values. This argument can take the same forms as those listed for Data B. Data E should have the same dimensions as Data B.

    • If either Data B or Data E is not a multi-cell array, then CHISQ.TEST reports a #VALUE! error.
    • If the dimensions of Data B and Data E are not identical, then CHISQ.TEST reports an invalid argument error (Err:502).
    • If any cell in either Data B or Data E contains non-numeric data, then CHISQ.TEST reports an invalid argument error (Err:502).
    • Any cell in Data B that is empty is ignored, along with the corresponding cell in Data E. Similarly, a cell in Data E that is empty is ignored, along with the corresponding cell in Data B. Note that CHISQ.TEST bases its estimate of the degrees of freedom on the number of rows and columns in the Data B and Data E array arguments, without taking account of the nature of the data within the arrays; if your data includes empty cells, this may cause unexpected results.
    • If all data in Data B and Data E is ignored because of empty cells, then CHISQ.TEST reports an invalid argument error (Err:502).
    • If any cell in Data E contains the value 0, then CHISQ.TEST reports a #DIV/0! error.

    Additional details:

    • Calc's CHITEST and CHISQ.TEST functions perform identical calculations. The requirements for CHITEST are specified in ODF 1.2; CHISQ.TEST is provided for interoperability with Microsoft Excel.
    • CHISQ.TEST calculates the chi-square statistic from the supplied data arrays using the formula:
    [math]\displaystyle{ \chi^2~=~\sum_{i=1}^{r}\sum_{j=1}^{c}\frac{(O_{ij}-E_{ij})^2}{E_{ij}} }[/math]
    where
    r is the number of rows in each array
    c is the number of columns in each array
    Oij is an element of the Data B array
    Eij is an element of the Data E array
    • To calculate the number of degrees of freedom, CHISQ.TEST uses the logic:
    If the number of rows (r) or the number of columns (c) is equal to 1 then the degrees of freedom (df) is set to [math]\displaystyle{ df\:=\:(c\:\times\:r)\:-\:1 }[/math].
    Otherwise df is set to [math]\displaystyle{ df\:=\:(c\:-\:1)\:\times\:(r\:-\:1) }[/math].
    • The probability returned by CHISQ.TEST is then calculated as:
    [math]\displaystyle{ \text{CHISQ.TEST}~=~\text{CHIDIST}(\chi^2;\:df) }[/math]
    • The name space for CHISQ.TEST is COM.MICROSOFT.CHISQ.TEST.
    • For more information about chi-square distributions, visit Wikipedia's Chi-squared test page.

    Examples:

    Worked example

    A die is rolled 1020 times and the numbers 1 through 6 are observed to come up 195, 151, 148, 189, 183, and 154 times respectively. In order to test a null hypothesis that the die is fair, a chi-square goodness of fit test is to be performed to assess the observed data.

    For N rolls of a die, the expected number of appearances of a particular number on the die is [math]\displaystyle{ \frac{N}{6} }[/math]. For 1020 rolls, this equals 170.

    Using the formula [math]\displaystyle{ \chi^2~=~\sum_{}^{}\frac{(O-E)^2}{E} }[/math] to calculate the chi-square statistic for the experiment gives the value 13.2705882352941.

    The following table summarizes these data.

    Data for fair / loaded dice experiment
    A B C
    1 Observed Expected chi-square
    2 195 170 3.67647058823529
    3 151 170 2.12352941176471
    4 148 170 2.84705882352941
    5 189 170 2.12352941176471
    6 183 170 0.994117647058824
    7 154 170 1.50588235294118
    8 1020 13.2705882352941

    The following notes clarify the above table:

    • Cells A2:A7 contain the observed values.
    • Cells B2:B7 contain the expected values.
    • Cell C2 contains the formula =POWER((A2-B2); 2)/B2, calculating one component of the overall chi-square value.
    • Cell C3 contains the formula =POWER((A3-B3); 2)/B3, and so on up to cell C7.
    • Cell C8 contains the formula =SUM(C2:C7), representing the overall value of the chi-square statistic for the experiment.

    Assuming 5 degrees of freedom, then the formula =CHIDIST(C8; 5) returns the value 0.0209708028742119.

    This is the same value returned by the formula =CHISQ.TEST(A2:A7; B2:B7).

    The interpretation of this right-tail probability is covered in more detail on the wiki page for the CHISQ.INV function.

    Other example

    Formula Description Returns
    =CHISQ.TEST({8;9;7;8}; {8;8;8;8}) Here the observed and expected data are passed through inline arrays. 0.969140404216273

    Related LibreOffice functions:

    CHIDIST

    CHIINV

    CHISQ.DIST

    CHISQ.DIST.RT

    CHISQ.INV

    CHISQ.INV.RT

    CHISQDIST

    CHISQINV

    CHITEST

    ODF standard:

    None

    Related (or similar) Excel functions:

    CHISQ.TEST