XL: Floating-Point Arithmetic May Give Inaccurate Results

• Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for the Macintosh, versions 2.x, 3.x, 4.x, 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition
• Microsoft Works for Windows, versions 2.0, 3.0
• Microsoft Works for Windows 95, version 4.0

SUMMARY

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 expression (.5-.4-.1) may be evaluated to the quantity (2.8E-17), or .000000000000000028.

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

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, as follows:

   .1

   .0001100011000111000111 (and so on)

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

In Microsoft Excel, it may be possible in some cases 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.

NOTE: If you use this option, you must format your numbers with a specific number format.

For additional information, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q48606
   TITLE     : Excel: Comparison of Values Does Not Return Correct Result