How to correct rounding errors in floating-point arithmetic

Article ID: 214118 - View products that this article applies to.
This article was previously published under Q214118
Expand all | Collapse all

On This Page

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.
Back to the top | Give Feedback

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.
Back to the top | Give Feedback

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:

Excel 2000, Excel 2002, and Excel 2003
  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.
Excel 2007
  1. Click the Microsoft Office Button, click Excel Options, and then click Advanced category.
  2. In the When calculating this workbook section, select the workbook that you want, click to select the Set precision as displayed check box, and then 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. Right-click the cells that you want to format, and then click Format Cells.
  2. On the Number tab, under Category, click Number.
  3. In the Decimal places box, select the precision (number of decimal places) that you want.
  4. Click OK.
Excel 2010
  1. Click on the File tab, click Excel Options, and then click Advanced category.
  2. In the When calculating this workbook section, select the workbook that you want, click to select the Set precision as displayed check box, and then 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. Right-click the cells that you want to format, and then click Format Cells.
  2. On the Number tab, under Category, click Number.
  3. In the Decimal places box, select the precision (number of decimal places) that you want.
  4. Click OK.
Back to the top | Give Feedback

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.
Back to the top | Give Feedback

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
78113
(http://support.microsoft.com/kb/78113/ )
Floating-point arithmetic may give inaccurate results

Excel 2007 and Excel 2010

For more information about how to change the precision of calculation, click Microsoft Office Excel Help on the toolbar, type change when and how formulas are calculated in Excel Help, and then click Search to view the topic.

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.
Back to the top | Give Feedback

Properties

Article ID: 214118 - Last Review: September 18, 2011 - Revision: 9.0
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Office Excel 2007
  • Microsoft Excel 2010
Keywords: 
kbprb KB214118
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top