Calculation Issues

Why Excel ROUND Returns Different Results for Calculated Values

John Wilson Oct 20, 2026 868 views

Question details

The ROUND function yields different results when applied to a formula's calculated value compared to a directly inputted decimal number.

Why Excel ROUND Returns Different Results for Calculated Values

Product: Excel
Device & OS: not provided
Scenario: Using the ROUND function on decimal values resulting from mathematical operations like subtraction.
Observed behavior: ROUND(A1,3) returns 0.015 when A1 contains '=1.031-1.0155', while ROUND(0.0155,3) correctly returns 0.016.

Before you start

Increase the decimal places of your calculated cell temporarily to see the true underlying binary value Excel is storing before applying any rounding functions.

Solution 1Recommended

Enable 'Set Precision as Displayed' Option

Force Excel to use the visible, displayed value instead of the underlying floating-point value for all calculations.

Since spreadsheet software uses binary floating-point arithmetic (IEEE 754 standard), many decimal fractions cannot be represented exactly. Enabling this feature forces the program to use the number exactly as it appears on the screen, bypassing microscopic calculation differences.

Open Excel Options

Click on 'File' in the top-left corner of the Excel window and select 'Options' from the bottom of the sidebar menu.

Navigate to Advanced Settings

In the Excel Options dialog box, click on the 'Advanced' tab on the left-hand panel.

Apply Precision Settings

Scroll down to the 'When calculating this workbook' section. Check the box for 'Set precision as displayed' and click 'OK' to confirm the change.

Enable 'Set Precision as Displayed' Option

Permanent Data Alteration: Using 'Set precision as displayed' will permanently remove underlying decimal accuracy for any number exceeding the currently displayed decimal places. Use this setting with caution.

Solution 2

Wrap Intermediate Calculations in a ROUND Function

Apply the ROUND function directly to the arithmetic operation to eliminate floating-point discrepancies before performing final calculations.

Precision Calculation Made Easy

Handle Complex Calculations Accurately with WPS Spreadsheet

WPS Spreadsheet offers powerful built-in calculation capabilities and precise control over formatting, ensuring your complex formulas and decimal rounding work seamlessly without unexpected arithmetic errors.

1. Open your file: Launch WPS Spreadsheet and open your workbook.
2. Access Options: Click on 'Menu' in the top-left corner, then click 'Options'.
3. Adjust Calculation Settings: Navigate to the 'Calculation' tab and check 'Precision as displayed' to resolve underlying decimal storage issues instantly.

100% compatible with Microsoft Excel formulas, including ROUND and complex arithmeticIntuitive built-in options to manage floating-point precision seamlesslyLightweight, fast, and completely free to download and use

Frequently Asked Questions

What is floating-point arithmetic and why does it affect Excel?

Floating-point arithmetic is a method computers use to represent and store decimal numbers in binary code. Because standard binary cannot represent certain decimal fractions precisely, microscopic deviations occur during calculation. This results in numbers like 0.0155 being stored as 0.0154999999999999.

Why does the subtraction result look perfectly correct on screen?

By default, Excel rounds cell displays to avoid showing microscopic fractional differences. While it displays 0.0155, the actual value stored in the system memory is slightly lower, which is why further ROUND functions treat it as a smaller number.

Is this rounding inaccuracy unique to Microsoft Excel?

No, this is a universal computing behavior based on the IEEE 754 standard for floating-point arithmetic. You will encounter the exact same behavior in other programs like WPS Spreadsheet, Google Sheets, and standard programming languages.

Excel & Data Problems