Article ID: 78113 - Last Review: October 25, 2012 - Revision: 12.0

This article was previously published under Q78113

This article discusses how Microsoft Excel stores and calculates floating-point numbers. This may affect the results of some numbers or formulas due to rounding and/or data truncation. ### Overview

Microsoft Excel was designed around the IEEE 754 specification with respect to storing and calculating floating-point 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 floating-point numbers should be stored in a binary computer. It is popular because it allows floating-point numbers to be stored in a reasonable amount of space and calculations to occur relatively quickly. The 754 standard is used in the floating-point units and numeric data processors of nearly all of today's PC-based microprocessors that implement floating-point 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

However, there are some limitations of the IEEE 754 specification which fall into three general categories:

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.8E-17 when it is stored.However, there are some limitations of the IEEE 754 specification which fall into three general categories:

- maximum/minimum limitations
- precision
- repeating binary numbers

- Underflow: Underflow occurs when a number is generated that is too small to be represented. In IEEE and Excel, the result is 0 (with the exception that IEEE has a concept of -0, and Excel does not).
- Overflow: Overflow occurs when a number is too large to be represented. Excel uses its own special representation for this case (#NUM!).

- Denormalized numbers: A denormalized number is indicated by
an exponent of 0. In that case, the entire number is stored in the mantissa and
the mantissa has no implicit leading 1. As a result, you lose precision, and
the smaller the number, the more precision is lost. Numbers at the small end of
this range have only one digit of precision.Example: A normalized number has an implicit leading 1. For instance, if the mantissa represents 0011001, the normalized number becomes 10011001 because of the implied leading 1. A denormalized number does not have an implicit leading one, so in our example of 0011001, the denormalized number remains the same. In this case, the normalized number has eight significant digits (10011001) while the denormalized number has five significant digits (11001) with leading zeroes being insignificant.

Denormalized numbers are basically a workaround to allow numbers smaller than the normal lower limit to be stored. Microsoft does not implement this optional portion of the specification because denormalized numbers by their very nature have a variable number of significant digits. This can allow significant error to enter into calculations. - Positive/Negative Infinities: Infinities occur when you divide by 0. Excel does not support infinities, rather, it gives a #DIV/0! error in these cases.
- Not-a-Number (NaN): NaN is used to represent invalid operations (such as infinity/infinity, infinity-infinity, or the square root of -1). NaNs allow a program to continue past an invalid operation. Excel instead immediately generates an error such as #NUM! or #DIV/0!.

Collapse this table

1 Sign Bit | 11 Bit Exponent | 1 Implied Bit | 52 Bit Mantissa |

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.2250738585072E-308, 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.

Floating-point numbers are represented in the following form, where

X = *Fraction* * 2^(*exponent* - *bias*)

Bias is the bias value used to avoid having to store negative exponents. The bias for single-precision numbers is 127 and 1,023 (decimal) for double-precision numbers. Excel stores numbers using double-precision.

A1: 1.2E+200 B1: 1E+100 C1: =A1+B1

A1: 0.000123456789012345 B1: 1 C1: =A1+B1

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.**

- In Excel 2003 and in earlier versions, click
**Options**on the**Tools**menu. - On the
**Calculation**tab, click to select the**Precision as displayed**check box.

- In Excel 2007, click the Microsoft Office Button, click
**Excel Options**, and then click the**Advanced**category. - In the
**When calculating this workbook**section, select the workbook you want, and then select the**Set precision as displayed**check box.

- In Excel 2013 and 2010, click
**File**, and then, and then click the**Options**category.**Advanced** - In the
section, select the workbook you want, and then select the**When calculating this workbook**check box.**Set precision as displayed**

For example, if you choose a number format showing two decimal places and then turn on the

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.8E-17, 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

Sub Main()
MySum = 0
For I% = 1 To 10000
MySum = MySum + 0.0001
Next I%
Debug.Print MySum
End Sub

- Enter the following into a new workbook:
A1: =(43.1-43.2)+1

- Right-click cell A1, and then click
**Format Cells**. On the**Number**tab, click**Scientific**under**Category**. Set the**Decimal places**to 15.

- In Excel 95 or earlier, enter the following into a new
workbook:
A1: =1.333+1.225-1.333-1.225

- Right-click
cell A1, and then click
**Format Cells**. On the**Number**tab, click**Scientific**under**Category**. Set the**Decimal places**to 15.

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
(http://support.microsoft.com/kb/214373/
)
Incorrect result raising 10 to very large/very small power

For more information about floating-point numbers
and the IEEE 754 specification, please see the following World Wide Web sites: http://www.ieee.org
(http://www.ieee.org)

http://steve.hollasch.net/cgindex/coding/ieeefloat.html (http://steve.hollasch.net/cgindex/coding/ieeefloat.html)

http://steve.hollasch.net/cgindex/coding/ieeefloat.html (http://steve.hollasch.net/cgindex/coding/ieeefloat.html)

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 floating-point arithmetic

- Microsoft Excel 2013
- Microsoft Excel 2010
- Microsoft Office Excel 2007
- Microsoft Office Excel 2003
- Microsoft Excel 2002 Standard Edition
- Microsoft Excel 2000 Standard Edition
- Microsoft Excel 97 Standard Edition
- Microsoft Excel 95 Standard Edition
- Microsoft Excel for Mac 2011
- Microsoft Excel 2008 for Mac
- Microsoft Excel 2004 for Mac
- Microsoft Excel X for Mac
- Microsoft Excel 2001 for Mac
- Microsoft Excel 98 for Macintosh

## Keywords: | kbinfo KB78113 |

Additional support options

Connect with customers and experts to get answers and share tips.

Not finding the solution you need. Reach out via e-mail, Twitter, phone or live chat.

Find a partner to get the service you need.

Get a local associate on the phone or visit us in person for face-to-face support.