XL: Problems With Statistical Functions and Large Numbers

ID: Q158071

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In Microsoft Excel, when you use any of the following statistical functions in a formula:

   STDEV
   STDEVP
   LINEST
   LOGEST
   KURT
   NEGBINOMDIST
   BINOMDIST

The formula may return an incorrect result.

CAUSE

This behavior may occur when the formula refers to very large numbers that, when summed, multiplied, or squared, exceed 15 digits in length.

STATUS

This behavior is by design of Microsoft Excel.

MORE INFORMATION

Microsoft Excel supports a maximum of 15 significant digits at all times. This limit applies to a value that is calculated by a formula. Because of this limitation, if at any time a formula calculates a value that exceeds 15 digits in length, digits beyond the fifteenth significant digit are changed to zeroes. This may cause the formula to return an incorrect result.

For example, when you use the STDEV function to calculate the standard deviation of a set of numbers, part of the function sums the numbers and then squares the result. If this number exceeds 15 digits in length, digits beyond the fifteenth digit are changed to zeroes. This affects the final result delivered by the formula. To see an example of this behavior, enter the following data in a worksheet:

   A1: 999999          B1: 99999999
   A2: 1000000         B2: 100000000
   A3: 1000001         B3: 100000001
   A4: =STDEV(A1:A3)   B4: =STDEV(B1:B3)

Although both formulas return the same standard deviation, 1, the second formula returns a zero. Because the square of the sum of the three values in B1:B3 is greater than 15 digits in length, digits beyond the fifteenth digit are changed to zeroes. This causes the formula to return an incorrect result.

This behavior may occur when you use any of the statistical functions listed in this article and you work with very large values. This is true because these functions all use squared values, which makes it probable that the limit of 15 significant digits is exceeded.

The exact formulas used by functions in Microsoft Excel are listed in the Help topic for each function in Microsoft Excel Help.

Additional query words: XL98 XL97 5.00 5.00a 5.00c 7.00 8.00

Keywords          : xlformula 
Version           : WINDOWS:5.0,5.0c,7.0,7.0a,97; MACINTOSH:5.0,5.0a,98
Platform          : MACINTOSH WINDOWS

Last Reviewed: January 8, 1999