Microsoft KB Archive/100104

= Microsoft Knowledge Base =

Excel: CHITEST May Return a #DIV/0! Error
Last reviewed: September 12, 1996

Article ID: Q100104

The information in this article applies to:


 * Microsoft Excel for Windows, versions 3.0, 4.0, and 4.0a
 * Microsoft Excel for OS/2, version 3.0
 * Microsoft Excel for the Macintosh, versions 3.0, 4.0

SUMMARY
In Microsoft Excel, the CHITEST function may return a #DIV/0! error when the expected range contains a zero. This is by design of the function. To eliminate the #DIV/0! error, set the zero value to a very small nonzero number such as 1E-20, 1E-100, or 1E-300.

MORE INFORMATION

The CHITEST function is used to return the value from the chi-squared distribution for the statistic and the appropriate degrees of freedom. CHITEST returns the test for independence. The syntax for CHITEST is as follows

=CHITEST(actual_range, expected_range) where actual range is the range of data that contains observations to test against the expected values. Expected_range is the range of data that contains the ratio of the product of row totals and column totals to the grand total.

It is not normal that a value of zero will ever appear in the expected range, therefore if a zero is contained in the expected range, the CHITEST function will return an error.

For example, enter the following data in a new worksheet.

A1:    85     B1:     35 A2:     5     B2:     25 A3:    10     B3:      0 In cell C1, type in the following formula:

=CHITEST(A1:A3,B1:B3) The cell containing the CHITEST function will display the error, #DIV/0!. To eliminate the error, replace the zero value in cell B3 with a small nonzero number such as 1E- 20.