How to correct rounding errors in floating-point arithmetic (214118)

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.

WORKAROUND

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:

On the Tools menu, click Options.
On the Calculation tab, under Workbook options, click to select the Precision as displayed check box.
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:

Select the cells that you want to format.
On the Format menu, click Cells.
On the Number tab, under Category, click Number.
In the Decimal places box, select the precision (number of decimal places) that you want.
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:

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.

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

78113 XL: Floating-point arithmetic may give inaccurate results

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.