Microsoft KB Archive/96435

= Microsoft Knowledge Base =

Excel: Unexpected Results with 0 Value in Statistical Function
Last reviewed: September 12, 1996

Article ID: Q96435

The information in this article applies to:


 * Microsoft Excel for Windows, versions 4.0 and 4.0a
 * Microsoft Excel for the Macintosh, version 4.0

SUMMARY
If you use an argument with a value of 0 (zero) with some of the statistical functions included in Microsoft Excel versions 4.0 (and Microsoft Excel version 4.0a for Windows), you may receive unexpected results.

MORE INFORMATION
Many of the statistical functions in Microsoft Excel calculate results by raising one argument to the power of another argument. Because 0 raised to the 0 power is an undefined mathematical concept, many statistical functions will return unexpected, although correct, results when you use arguments that have a zero value.

Microsoft Excel evaluates functions based on the following two rules:


 * 0 raised to any power equals 0.
 * Any number raised to the zero power equals 1.

For example, with the statistical function BINOMDIST, a portion of the result comes from raising the argument probability_s (p) to the power of the argument number_s (x). If an event has a 0-percent chance of success, logically the probability of no successes in 10 trials should be 1 (or 100-percent), as in the following example:

=BINOMDIST(0,10,0.0000000001,FALSE) will return the value of 1 However, if the value for the probability_s or number_s argument is 0, the results will be different than if the value for either of these arguments is 0.00000000000001:

=BINOMDIST(0,10,0,FALSE) will return the value of 0. Microsoft Excel is mathematically correct in both cases, although logically you would expect both functions to return a result of 1.

The following statistical functions may exhibit this same behavior:

BINOMDIST CRITBINOM GAMMADIST NEGBINOMDIST WEIBULL For more information about the BINOMDIST function, see pages 38 and 39 of the &quot;Microsoft Excel Function Reference&quot;, version 4.0.