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 Excel 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.
The function is new in Excel 2010, and so is not available in earlier versions of Excel. However, the Chisq.Test function is simply an updated version of the Chitest Function, that is available in earlier versions of Excel.
The syntax of the function is :
Where the function arguments are:
|actual_range||-||An array of observed frequencies|
|expected_range||-||An array of expected frequencies|
The supplied actual_range and the expected_range arrays must have equal dimensions.
It is important to note that the chi-square test is not reliable with very small expected values. As a guideline, if any of the expected values are less than 5, or if the total of the expected values is less than 50, the result of the chi-square test should not be relied upon.
The spreadsheet below contains the observed and expected frequencies of the responses of two groups, (men and women), to a simple question.
The Excel Chisq.Test function can be used to calculate the chi-square test for independence, for the above data sets. The formula for this is:
=CHISQ.TEST( B3:C5, F3:G5 )
which gives the result 0.000699103.
Generally, a probability of 0.05 or less is considered to be significant. Therefore, the above returned value, 0.000699103, indicates a significant difference between the observed and the expected frequencies, which is unlikely to be due to sampling error.
Further examples of the Excel Chisq.Test function can be found on the Microsoft Office website.
If you get an error from the Excel Chisq.Test function this is likely to be one of the following :
Occurs if either:
|#DIV/0!||-||Occurs if any of the expected_values are zero|
|#NUM!||-||Occurs if any of the expected_values are negative|