The Excel CHISQ.TEST Function

Related Functions:

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:

Chi-Square Distribution Equation


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.

Basic 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.

Example Data for the Excel Chisq.Test Function

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.

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

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 zero.
#NUM!-Occurs if any of the values in the supplied expected_range are negative.