Incorrect Result Raising 10 to Very Large/Very Small Power

Article translations Article translations
Article ID: 172911 - View products that this article applies to.
This article was previously published under Q172911
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

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 very large power or a very small power, the formula may return an incorrect result:
   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 to a very large power or a very small power; for example, =9^10000000000 correctly returns a #NUM! error.

CAUSE

These problems occur when you enter a formula in which the value 10 is raised 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 largest positive 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 smallest positive 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 your workbooks 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 products listed at the beginning of this article.

MORE INFORMATION

Microsoft Excel supports positive values almost as large as 10^308 (a one followed by 308 zeroes) and almost as small as 10^-308 (a decimal point, followed by 308 zeroes and a one). Similar negative values are also supported. Smaller values are rounded to 0, and larger values are converted into a #NUM! error value.

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

With respect to the largest formula shown above: note that a googol is 10^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), is a 1 followed by 10^308 zeroes. So, the result of the third formula is equivalent to the following:
googolplex*googolplex*googolplex*(10^(10^8))
So, it would take over (1 followed by 10^307 zeroes) universes to contain (1 followed by 10^308 zeroes) elementary particles.

Properties

Article ID: 172911 - Last Review: October 7, 2013 - Revision: 1.1
APPLIES TO
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 98 for Macintosh
Keywords: 
kbnosurvey kbarchive kbbug kbpending KB172911

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com