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.
REFERENCES
"Microsoft Excel Function Reference," version 4.0, pages 52-53
KBCategory: kbother Last reviewed: September 12, 1996 |