Statistical - CHISQ.TEST Function

Description

The CHISQ.TEST function returns the test for independence. CHISQ.TEST returns the value from the chi-squared (χ2) distribution for the statistic and the appropriate degrees of freedom. You can use χ2 tests to determine whether hypothesized results are verified by an experiment.

Syntax

CHISQ.TEST (actual_range,expected_range)

Arguments

Notes

• The X² test first calculates a X² statistic using the formula −

  $$X^2=\sum_{i=1}^{r} \sum_{j=1}^{c} \frac{\left ( A_{ij}-E_{ij} \right )^2}{E_{ij}}$$

  Where,

  $A_{ij}$ = actual frequency in the i-th row, j-th column

  $E_{ij}$ = expected frequency in the i-th row, j-th column

  $r$ = number or rows

  $c$ = number of columns

  If actual_range and expected_range have a different number of data points, CHISQ.TEST returns the #N/A error value.

• A low value of X² is an indicator of independence. As can be seen from the formula, X² is always positive or 0, and is 0 only if $A_{ij}$ = $E_{ij}$ for every i,j

• CHISQ.TEST returns the probability that a value of the χ2 statistic at least as high as the value calculated by the above formula could have happened by chance under the assumption of independence

  • In computing this probability, CHISQ.TEST uses the X² distribution with an appropriate number of degrees of freedom, df

  • If r > 1 and c > 1, then df = (r - 1)(c - 1)

  • If r = 1 and c > 1, then df = c – 1

  • If r > 1 and c = 1, then df = r – 1

  • r = c= 1 is not allowed and #N/A is returned

• Use of CHISQ.TEST is most appropriate when $E_{ij's}$ are not too small. Some statisticians suggest that each $E_{ij}$ should be greater than or equal to 5.

• If any of the values in the expected_range is negative, CHISQ.TEST returns #NUM! error.

Applicability

Excel 2010, Excel 2013, Excel 2016

Example