# 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 |