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.
Syntax: CHISQ.TEST( actual_range, expected_range )
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.
Chisq.Test Function Example
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.
Chisq.Test Function Errors
If you get an error from the Excel Chisq.Test function this is likely to be one of the following :
|#N/A||–||Occurs if either the two supplied data arrays have different dimensions or the supplied data arrays contain just one value (i.e. have length = 1 and width = 1)|
|#DIV/0!||–||Occurs if any of the expected_values are zero|
|#NUM!||–||Occurs if any of the expected_values are negative|