Microsoft KB Archive/214118

= How to correct rounding errors in floating-point arithmetic =

Article ID: 214118

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Office Excel 2003

-



This article was previously published under Q214118



SYMPTOMS
Many combinations of arithmetic operations on floating-point numbers in Microsoft Excel and Microsoft Works may produce results that appear to be incorrect by very small amounts. For example, the equation

=1*(.5-.4-.1)

may be evaluated to the quantity (-2.78E-17), or -0.0000000000000000278 instead of 0.



CAUSE
This behavior is not a problem in or a limitation of Excel or Works; this behavior occurs because the Institute of Electrical and Electronics Engineers (IEEE) 754 floating-point standard requires that numbers be stored in binary format.



Method 1
To minimize any effects of floating point arithmetic storage inaccuracy, use the Round function to round numbers to the number of decimal places that is required by your calculation. For example, if you are working with currency, you would likely round to 2 decimal places:

=ROUND(1*(0.5-0.4-0.1),2)

Method 2
You can frequently prevent floating point 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 precision that is displayed on the worksheet.

Note Use of the Precision as Displayed option can have cumulative calculation effects that can make your data increasingly inaccurate over time. Use this option only if you are certain that the displayed percision will maintain the accuracy of your data.

To use the Precision as displayed option, follow these steps:
 * 1) On the Tools menu, click Options.
 * 2) On the Calculation tab, under Workbook options, click to select the Precision as displayed check box.
 * 3) Click OK.

If you use the Precision as displayed option, you must format your numbers by using a specific number format.

To format cells to a specific number precision, follow these steps:
 * 1) Select the cells that you want to format.
 * 2) On the Format menu, click Cells.
 * 3) On the Number tab, under Category, click Number.
 * 4) In the Decimal places box, select the precision (number of decimal places) that you want.
 * 5) Click OK.



MORE INFORMATION
The IEEE 754 standard is a method of storing floating-point numbers in a compact way that is easy to manipulate. This standard is used by Intel coprocessors and most PC-based programs that implement floating-point math.

IEEE 754 specifies that numbers be stored in binary format to reduce storage requirements and allow the built-in binary arithmetic instructions that are available on all microprocessors to process the data in a relatively rapid fashion. However, some numbers that are simple, nonrepeating decimal numbers are converted into repeating binary numbers that cannot be stored with perfect accuracy.

For example, the number 1/10 can be represented in a decimal number system with a simple decimal:

.1

However, the same number in binary format becomes the repeating binary decimal:

.0001100011000111000111 (and so on)

This number cannot be represented in a finite amount of space. Therefore, this number is rounded down by approximately -2.78E-17 when it is stored.

If several arithmetic operations are performed to obtain a given result, these rounding errors may be cumulative.



Excel 2002 and Excel 2003
For more information about changing the precision of calculation, click Microsoft Excel Help on the Help menu, type change when and how formulas are calculated in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Excel 2000
For more information about changing the precision of calculation, click Microsoft Excel Help on the Help menu, type change the way Microsoft Excel calculates formulas in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: XL

Keywords: kbprb KB214118

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.