Article ID: 78113  View products that this article applies to. This article was previously published under Q78113 On This PageSummaryThis article discusses how Microsoft Excel stores and calculates floatingpoint numbers. This may affect the results of some numbers or formulas due to rounding and/or data truncation. OverviewMicrosoft Excel was designed around the IEEE 754 specification with respect to storing and calculating floatingpoint numbers. IEEE is the Institute of Electrical and Electronics Engineers, an international body that, among other things, determines standards for computer software and hardware. The 754 specification is a very widely adopted specification that describes how floatingpoint numbers should be stored in a binary computer. It is popular because it allows floatingpoint numbers to be stored in a reasonable amount of space and calculations to occur relatively quickly. The 754 standard is used in the floatingpoint units and numeric data processors of nearly all of today's PCbased microprocessors that implement floatingpoint math, including the Intel, Motorola, Sun, and MIPS processors.When storing numbers, a corresponding binary number can represent every number or fractional number. For example, the fraction 1/10 can be represented in a decimal number system as 0.1. However, the same number in binary format becomes the repeating binary decimal
0001100110011100110011 (and so on)
and can be infinitely repeated. This number cannot be represented in a finite (limited) amount of space. Therefore, this number is rounded down by approximately 2.8E17 when it is stored.However, there are some limitations of the IEEE 754 specification which fall into three general categories:
More informationMaximum/Minimum LimitationsAll computers have a maximum and a minimum number that can be handled. Because the number of bits of memory in which the number is stored is finite, it follows that the maximum or minimum number that can be stored is also finite. For Excel, the maximum number that can be stored is 1.79769313486232E+308 and the minimum positive number that can be stored is 2.2250738585072E308.Cases Where We Adhere to IEEE 754
Cases Where We Do Not Adhere to IEEE 754
PrecisionA floatingpoint number is stored in binary in three parts within a 65bit range: the sign, the exponent, and the mantissa.Collapse this table
The mantissa and the exponent are both stored as separate components. As a result, the amount of precision possible may vary depending on the size of the number (the mantissa) being manipulated. In the case of Excel, although Excel can store numbers from 1.79769313486232E308 to 2.2250738585072E308, it can only do so within 15 digits of precision. This limitation is a direct result of strictly following the IEEE 754 specification and is not a limitation of Excel. This level of precision is found in other spreadsheet programs as well. Floatingpoint numbers are represented in the following form, where exponent is the binary exponent:
X = Fraction * 2^(exponent  bias) Fraction is the normalized fractional part of the number, normalized
because the exponent is adjusted so that the leading bit is always a 1. This
way, it does not have to be stored, and you get one more bit of precision. This
is why there is an implied bit. This is similar to scientific notation, where
you manipulate the exponent to have one digit to the left of the decimal point;
except in binary, you can always manipulate the exponent so that the first bit
is a 1, because there are only 1s and 0s.Bias is the bias value used to avoid having to store negative exponents. The bias for singleprecision numbers is 127 and 1,023 (decimal) for doubleprecision numbers. Excel stores numbers using doubleprecision. Example Using Very Large NumbersEnter the following into a new workbook:A1: 1.2E+200 B1: 1E+100 C1: =A1+B1 Example Using Very Small NumbersEnter the following into a new workbook:A1: 0.000123456789012345 B1: 1 C1: =A1+B1 Correcting Precision ErrorsExcel offers two basic methods to compensate for rounding errors: the ROUND function and the Precision as displayed or Set precision as displayed workbook option.Method 1: The ROUND FunctionThe following example using the data above, uses the ROUND function to force a number to five digits. This allows you to successfully compare the result to another value.A1: 1.2E+200 B1: 1E+100 C1: =ROUND(A1+B1,5)
results in 1.2E+200.
D1: =IF(C1=1.2E+200, TRUE, FALSE)
results in the value TRUE. Method 2: Precision as DisplayedIn some cases, you may be able to prevent rounding errors from affecting your work by using the Precision as displayed option. This option forces the value of each number in the worksheet to be the displayed value. To turn on this option, follow these steps:
For example, if you choose a number format showing two decimal places and then turn on the Precision as displayed option, all accuracy beyond two decimals will be lost when you save your workbook. This option affects the active workbook including all worksheets. You cannot undo this option and recover the lost data. It is recommended that you save your workbook prior to enabling this option. Repeating Binary Numbers and Calculations with Results Close to ZeroAnother confusing problem with storing floating point numbers in binary is that some numbers, which are finite, nonrepeating numbers in decimal base 10, are infinite, repeating numbers in binary. The most common example of this is the value 0.1 and variants thereof. Although these numbers can be represented perfectly in base 10, the same number in binary format becomes the following repeating binary number when stored in the mantissa:
000110011001100110011 (and so on)
The IEEE 754 specification makes no special provision for any number; it stores what it can in the mantissa and truncates the rest. This results in an error of about 2.8E17, or 0.000000000000000028 when stored.Even common decimal fractions, such as decimal 0.0001, cannot be represented exactly in binary. (0.0001 is a repeating binary fraction with a period of 104 bits). This is similar to why the fraction 1/3 cannot be exactly represented in decimal (a repeating 0.33333333333333333333). This explains why a simple example in Microsoft Visual Basic for Applications
Example Adding a Negative Number
Example When a Value Reaches Zero
Excel 97, however, introduced an optimization that attempts to correct for this problem. Should an addition or subtraction operation result in a value at or very close to zero, Excel 97 and later will compensate for any error introduced as a result of converting an operand to and from binary. The example above when performed in Excel 97 and later correctly displays 0 or 0.000000000000000E+00 in scientific notation. For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base: 172911
(http://support.microsoft.com/kb/172911/
)
Incorrect result raising 10 to very large/very small power
214373 For more information about floatingpoint numbers
and the IEEE 754 specification, please see the following World Wide Web sites:
(http://support.microsoft.com/kb/214373/
)
Incorrect result raising 10 to very large/very small power
http://www.ieee.org
(http://www.ieee.org)
http://steve.hollasch.net/cgindex/coding/ieeefloat.html
(http://steve.hollasch.net/cgindex/coding/ieeefloat.html)
References For more information about how to work
around these errors, click the following article number to view the article in
the Microsoft Knowledge Base: 214118
(http://support.microsoft.com/kb/214118/
)
How to correct rounding errors in floatingpoint arithmetic
PropertiesArticle ID: 78113  Last Review: October 25, 2012  Revision: 12.0 Applies to

Contact us for more help 