Related Functions:

The CHISQ.TEST function performs the chi-square test on two supplied data sets of observed and expected frequencies, and returns the probability that the differences between the sets are simply due to sampling error.

  • This function was introduced in Excel 2010 and so is not available in earlier versions.
  • CHISQ.TEST replaces the CHITEST function included in earlier versions of Excel.

Syntax

=CHISQ.TEST(actual_range,expected_range)

Arguments

Argument Description
actual_range The range of data that contains observations to test against expected values
expected_range The range of data that contains the ratio of the product of row totals and column totals to the grand total

Examples

  A B C D E F G H
1   Observed Frequencies     Expected Frequencies  
2   Men Women     Men Women  
3 Agree 32 38   Agree 26.5 31.75  
4 Disagree 62 61   Disagree 57.25 59.8  
5 Neutral 6 1   Neutral 16.25 8.45  
6                
7 Formula   Result Notes
8 =CHISQ.TEST(B3:C5,F3:G5)   0.000366 The x2 statistic for the data above data with 2 degrees of freedom

Common Function Error(s)

Problem What went wrong
#NUM! Occurs if any of the values in the supplied expected_range are negative
#N/A Occurs if either:

  the two supplied data arrays have different dimensions
  the supplied data arrays contain just one value, i.e. have length = 1 and width = 1
#DIV/0! Occurs if any of the values in the supplied expected_range are negative

The chi-square test uses the chi-square distribution, to test whether there is a significant difference between observed frequencies and expected frequencies for a data set.

The chi-square distribution is given by the formula:    

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

where:

Aij = actual frequency in the i’th row & j’th column
Eij = expected frequency in the i’th row & j’th column
r = number of rows
c = number of columns

The chi-square test gives an indication of whether the value of the chi-square distribution, for independent sets of data, is likely to have occurred by chance alone.

See Wikipedia for more information on the chi-square test.