CHITEST Function

Basic Description

The Excel CHITEST function uses the chi-square test to calculate the probability that the differences between two supplied data sets (of observed and expected frequencies), are likely to be simply due to sampling error, or if they are likely to be real.

Syntax: CHITEST( actual_range, expected_range )

Where the function arguments are:

actual_range An array of observed frequencies
expected_range An array of expected frequencies (must have the same dimension as the actual_range array)


You should bear in mind that the chi-square test is not reliable when the expected values are too small. 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, you should not rely on the result of the chi-square test.


Chitest Function Example

Cells B3-C5 and F3-G5 of the spreadsheet below show the observed and expected frequencies of responses from men and women to a simple question.

Example Data for the Excel Chitest Function

The chi-square test for independence, for the above data sets, is calculated using the Excel Chitest function as follows:

=CHITEST( B3:C5, F3:G5 )

This gives the result 0.000699103.

Generally, a probability of 0.05 or less is considered to be significant. Therefore, the returned value of 0.000699103 is highly significant.


Chitest Function Errors

If you get an error from the Excel Chitest function this is likely to be one of the following :


Occurs if either the two supplied data arrays have different dimensions or the supplied data arrays have width and height 1 (i.e. contain just one value)

#DIV/0! Occurs if any of the supplied expected_values are zero
#NUM! Occurs if any of the supplied expected_values are negative

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



clearPost Comment