Incorrect Result Raising 10 to Very Large/Very Small Power

This article was previously published under Q172911
This article has been archived. It is offered "as is" and will no longer be updated.
SYMPTOMS
In the versions of Microsoft Excel listed at the beginning of this article,if you enter a formula in which the value 10 is raised to either a verylarge power or a very small power, the formula may return an incorrectresult:
   10 Raised to       Expected Result   Actual Result   -------------------------------------------------------------------   very large power   #NUM! error       0, 0.1   very small power   0                 #DIV/0! error, 10^<some value>
NOTE: These problems do not occur in earlier versions of Microsoft Excel.Also, the problems do not occur when you raise a value other than 10 toa very large power or a very small power; for example, =9^10000000000correctly returns a #NUM! error.
CAUSE
These problems occur when you enter a formula in which the value 10 israised to a power in one of the following ranges:

  • greater than or equal to 2^31 (2,147,483,648) and less than or equal to 10^308 (1 followed by 308 zeroes) -or-

  • less than or equal to -(2^31) (-2,147,483,648) and greater than or equal to -(10^308) (-1 followed by 308 zeroes)
For example:
   Formula you type          Value returned   ----------------------------------------   =10^2147483648            0   =10^10000000000           0.1   =10^(10^308)              0.1				
These formulas should all return a #NUM! error, because the largestpositive number allowed in Microsoft Excel is 9.99999999999999E+307,which is just less than 10^308.Or:
   Formula you type          Value returned   ----------------------------------------   =10^-2147483648           #DIV/0!   =10^-4294966989           1E+307   =10^-4294967295           10   =10^-(10^308)             10				
These formulas should all return the value 0, because the smallestpositive number allowed in Microsoft Excel is 1E-307,which is just above 0.
WORKAROUND
To prevent this problem from occurring, make sure that formulas in yourworkbooks do not raise the number 1.797 to a power larger than 308 or the number 2.225 smaller than -308.
STATUS
Microsoft has confirmed this to be a problem in the Microsoft productslisted at the beginning of this article.
MORE INFORMATION
Microsoft Excel supports positive values almost as large as 10^308 (aone followed by 308 zeroes) and almost as small as 10^-308 (a decimalpoint, followed by 308 zeroes and a one). Similar negative values arealso supported. Smaller values are rounded to 0, and larger values areconverted into a #NUM! error value.

For comparison, note that the estimated number of elementary particles inthe known universe is 10^80, or a one followed by 80 zeroes. The smallestvalue used in physics is roughly 10^-33 centimeters, the scale at whichquantum fluctuations are believed to exist. These values are well withinthe limits supported by Microsoft Excel; so, Microsoft Excel can workwith any meaningful number in the universe.

With respect to the largest formula shown above: note that a googol is10^100, or a 1 followed by 100 zeroes, and that a googolplex is 10^googol,or a 1 followed by 10^100 zeroes.

The correct result of the third formula shown above, =10^(1E+308), isa 1 followed by 10^308 zeroes. So, the result of the third formula isequivalent to the following:
googolplex*googolplex*googolplex*(10^(10^8))
So, it would take over (1 followed by 10^307 zeroes) universes tocontain (1 followed by 10^308 zeroes) elementary particles.
XL97 googol googolplex mantissa exponent XL
Properties

Article ID: 172911 - Last Review: 10/07/2013 05:16:24 - Revision: 1.1

  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 98 for Macintosh
  • kbnosurvey kbarchive kbbug kbpending KB172911
Feedback