Calculation Issues

How to Fix Excel Floating-Point Errors in Decimal Subtraction

Elise Williams Oct 22, 2026 870 views

Question details

Users encounter unexpected long decimal numbers when performing basic decimal subtraction, expecting a clean two-decimal result.

How to Fix Floating-Point Errors in Excel Decimal Subtraction

Product: Microsoft Excel
Device & OS: not provided
Scenario: Subtracting decimal numbers that appear to have only two decimal places (e.g., currency values) within a spreadsheet.
Observed behavior: Excel displays a floating-point approximation error, such as -7,788.57000000001, instead of the exact two-decimal result (-7,788.57).

Before you start

Before altering your formulas, determine whether you need the exact mathematical value corrected for downstream calculations or if you simply want to visually hide the extra decimal places on your sheet.

Solution 1Recommended

Use the ROUND Function to Correct Precision

Explicitly round the calculation formula to exactly two decimal places to ensure calculation accuracy for future formulas.

Excel uses 64-bit binary floating-point architecture to store numbers. Because some base-10 decimal fractions (like 0.13 or 0.70) cannot be represented exactly in binary format, they are stored as approximations. This creates microscopic differences that become visible during subtraction.

Using the ROUND function is the safest and most consistent workaround to eliminate these visible approximation errors without globally altering workbook precision settings.

Select the target cell

Click on the cell containing your decimal subtraction formula that is displaying the floating-point error.

Modify the formula

In the formula bar, wrap your existing calculation with the ROUND function. For example, change =A1-B1 to =ROUND(A1-B1, 2).

Apply the calculation

Press Enter. The underlying value is now strictly limited to two decimal places, fixing the long trailing decimal error.

Use the ROUND Function to Correct Precision

Recommended approach: This method ensures that any other formulas referencing this cell will use the exact two-decimal value, preventing calculation errors from compounding.

Solution 2

Change Cell Number Formatting (Display Only)

Adjust the cell format to display only two decimal places if you do not mind the underlying value retaining its floating-point approximation.

Powerful Spreadsheet Tool

Fix Decimal Calculation Errors Easily with WPS Spreadsheet

WPS Spreadsheet offers advanced formula capabilities and number formatting identical to Excel, helping you easily manage floating-point precision issues while working seamlessly with your existing data.

1. Open your file in WPS: Launch WPS Spreadsheet and open your existing workbook containing the decimal subtraction formulas.
2. Locate the error: Find the cell displaying the unwanted trailing decimal values (e.g., .00000001).
3. Apply the ROUND function: Click the formula bar, type =ROUND( directly before your subtraction equation, and add ,2) at the end.
4. Confirm the fix: Press Enter to instantly correct the internal precision calculation to a clean two-decimal result.

Fully compatible with Microsoft Excel (.xlsx, .xls) formatsSupports all standard Excel functions including the ROUND function for fixing floating-point errorsFree and lightweight with an intuitive, familiar tabbed interfaceCross-platform support allowing seamless migration from Office

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel add .00000001 at the end of my subtraction?

This occurs because Excel and most spreadsheet programs store numbers as 64-bit binary floating-point approximations. Because some base-10 decimal fractions cannot be represented perfectly in binary, microscopic discrepancies appear during subtraction calculations.

Should I use the 'Precision as displayed' setting to fix floating-point errors?

It is generally not recommended unless you fully understand its impact. While it forces Excel to use the displayed value for calculations, it permanently changes the stored values across your entire workbook, which can cause inconsistent or irreversible data loss.

Does changing the cell format to 2 decimal places fix the actual number?

No. Changing the cell format only alters how the number looks visually on your screen. The underlying floating-point approximation is still retained and will be used in any subsequent calculations referencing that cell. Use the ROUND function to fix the actual stored value.

Excel & Data Problems