Microsoft Office Tutorials and References
In Depth Information
Dealing with Floating-Point Number Problems
One way to handle these “very close to zero” rounding errors is to use a formula like this:
This formula uses the less-than operator to compare the absolute value of the number with a
very small number. This formula would return Zero.
Another option is to instruct Excel to change the worksheet values to match their displayed
format. To do this, access the Excel Options dialog box (choose File
Options) and click the
Advanced tab. Select the Set Precision as Displayed check box (which is located in the When
Calculating This Workbook section).
Selecting the Precision as Displayed option changes the numbers in your worksheets
to permanently match their appearance on-screen. This setting applies to all sheets in
the active workbook. Most of the time, this option is not what you want. Make sure
that you understand the consequences of using the Set Precision as Displayed option.