OverviewMicrosoft Excel was designed around the IEEE 754 specification to determine how it stores and calculates 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 numbers are stored, 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 following repeating binary decimal:
However, there are some limitations of the IEEE 754 specification that fall into three general categories:
- Maximum/minimum limitations
- Repeating binary numbers
Maximum/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.2250738585072E-308.
Cases in which we adhere to IEEE 754
- 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!).
Cases in which we do not adhere to IEEE 754
- 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!.
PrecisionA floating-point number is stored in binary in three parts within a 65-bit range: the sign, the exponent, and the mantissa.
|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 exponent is the binary exponent:
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.
Example using very large numbersEnter the following into a new workbook:
The resulting value in cell C1 would be 1.2E+200, the same value as cell A1. In fact if you compare cells A1 and C1 using the IF function, for example IF(A1=C1), the result will be TRUE. This is caused by the IEEE specification of storing only 15 significant digits of precision. To be able to store the calculation above, Excel would require at least 100 digits of precision.
Example using very small numbersEnter the following into a new workbook:
The resulting value in cell C1 would be 1.00012345678901 instead of 1.000123456789012345. This is caused by the IEEE specification of storing only 15 significant digits of precision. To be able to store the calculation above, Excel would require at least 19 digits of precision.
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 functionUsing the previous data, the following example uses the ROUND function to force a number to five digits. This lets you successfully compare the result to another value.
D1: =IF(C1=1.2E+200, TRUE, FALSE)
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.
- On the File menu, click Options, and then click the Advanced category.
- In the When calculating this workbook section, select the workbook that you want, and then select the Set precision as displayed check box.
For example, if you choose a number format that shows two decimal places, and then you turn on the Precision as displayed option, all accuracy beyond two decimal places is 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. We recommend that you save your workbook before you enable this option.
Repeating binary numbers and calculations that have near-zero resultsAnother confusing problem that affects the storage of floating point numbers in binary format is that some numbers that are finite, non-repeating numbers in decimal base 10, are infinite, repeating numbers in binary. The most common example of this is the value 0.1 and its variations. Although these numbers can be represented perfectly in base 10, the same number in binary format becomes the following repeating binary number when it is stored in the mantissa:
Even common decimal fractions, such as decimal 0.0001, cannot be represented exactly in binary. (0.0001 is a repeating binary fraction that has a period of 104 bits). This is similar to why the fraction 1/3 cannot be exactly represented in decimal (a repeating 0.33333333333333333333).
For example, consider the following simple example in Microsoft Visual Basic for Applications:
MySum = 0
For I% = 1 To 10000
MySum = MySum + 0.0001
Example: Adding a negative number
- Enter the following into a new workbook:
- Right-click cell A1, and then click Format Cells. On the Number tab, click Scientific under Category. Set the Decimal places to 15.
Example when a value reaches zero
- In Excel 95 or earlier, enter the following into a new workbook:
- Right-clickcell 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:
Article ID: 78113 - Last Review: Jan 4, 2017 - Revision: 1