Calculation Issues

How to Fix Floating-Point Subtraction Differences in Excel

WPS Editor Oct 10, 2026 872 views

Question details

The user needs to correct a subtraction formula that returns an unexpected long decimal value instead of a clean, two-decimal number.

How to Fix Floating-Point Subtraction Differences in Excel

Product: Excel
Device & OS: not provided
Scenario: Subtracting decimal values that visually appear rounded to two decimal places.
Observed behavior: Excel returns a floating-point difference, such as 59.7000000000003 instead of 59.70, even when intermediate formulas use the ROUND function.

Before you start

Verify which cells contain the final calculation results, as applying the rounding function directly to the final output is the most effective way to handle binary precision errors.

Solution 1Recommended

Use the ROUND Function on the Final Result

Wrap your final subtraction formula in the ROUND function to permanently eliminate binary precision errors from the calculation.

Spreadsheet software uses binary floating-point arithmetic to store and calculate numbers. Because some decimal numbers cannot be represented exactly in binary code, tiny precision errors can accumulate during calculations. This results in numbers like 59.7000000000003 instead of 59.70.

Even if intermediate numbers are rounded, the final subtraction can still produce floating-point artifacts. Rounding the very last step of your calculation ensures the displayed value and stored value are exact.

Select the result cell

Click on the cell containing your subtraction formula where the floating-point error is displayed.

Edit the formula

Click into the formula bar at the top of the screen to edit your existing subtraction formula (for example, =Z4-R4).

Apply the ROUND function

Wrap the existing formula with the ROUND function and specify the desired number of decimal places. Type =ROUND(Z4-R4, 2) to round to two decimal places.

Execute the calculation

Press Enter to apply the updated formula. The result will now calculate perfectly to two decimal places without trailing floating-point errors.

Use the ROUND Function on the Final Result

Formatting vs. Rounding: Simply changing the cell format to display fewer decimal places only alters how the number looks, not the actual stored value. The ROUND function changes the stored value, ensuring downstream calculations are accurate.

Calculate Data with Precision in WPS Spreadsheet

WPS Spreadsheet offers a highly accurate calculation engine that fully supports standard mathematical functions like ROUND, helping you eliminate floating-point errors effortlessly.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing the subtraction error.
2. Locate the formula: Click on the cell that displays the incorrect floating-point decimal result.
3. Update with ROUND: In the formula bar, modify your calculation to include the ROUND function, such as =ROUND(A1-B1, 2).
4. Confirm the result: Press Enter to apply the change and get your exact decimal value without floating-point artifacts.

100% compatible with Microsoft Excel formulas and file formats (.xlsx)Built-in advanced calculation engine for precise data handlingFree, lightweight, and fast to launch on any deviceFamiliar user interface with zero learning curve for seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show numbers like 0.0000000000001 instead of 0?

This happens due to the IEEE 754 standard for binary floating-point arithmetic used by Excel and most software. Decimals often cannot be stored exactly in binary memory, leaving tiny residual values during calculations.

Does formatting the cell to 2 decimal places fix the precision error?

No, cell formatting only changes how the number looks visually on the screen. The underlying precision error remains stored in the cell and can cause errors in dependent formulas unless you use the ROUND function.

What is the 'Set precision as displayed' option?

It is a workbook setting found in Options > Advanced that forces the software to permanently change stored values to match their displayed formatting. While it can fix floating-point errors globally, it should be used with extreme caution because it permanently permanently deletes underlying precise data.

Excel & Data Problems