The Excel CHISQ.TEST Function
CHI-SQUARE TEST
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:
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.
Function Description
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 Chisq.Test function is:
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.
For further examples of the Excel Chisq.Test function, see the Microsoft Office website.
Chisq.Test Function Errors
If you get an error from the Excel Chisq.Test function this is likely to be one of the following:
Common Errors
| #N/A | - |
Occurs if either:
|
| #DIV/0! | - | Occurs if any of the values in the supplied expected_range is zero. |
| #NUM! | - | Occurs if any of the values in the supplied expected_range is negative. |