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
KBSubcategory:

Additional reference words: 3.00 4.00 4.00a M_eXceL non-zero



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 12, 1996
©1997 Microsoft Corporation. All rights reserved. Legal Notices.