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.
|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|
|1||Observed Frequencies||Expected Frequencies|
|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:
|#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:
|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.