Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the Rounding error
Excel & Google SheetsWhat Rounding means
Computers represent decimal fractions in binary (base-2) floating-point, which cannot express most decimal fractions exactly. Values like 0.1 and 0.3 are stored as repeating binary fractions, so arithmetic on them produces tiny rounding residuals (e.g., 1.0000000000000002 instead of 1). These residuals are usually invisible but can break IF comparisons and make totals appear to not sum correctly.
Common causes
- Direct floating-point arithmetic on decimal fractions (e.g., 0.1 + 0.2) produces a tiny residual error that appears when enough decimal places are shown.
- A chain of calculations accumulates many small rounding residuals, causing the final total to differ by a penny or a fraction from the expected result.
- An IF or comparison formula (e.g., =IF(A2=0.3,...)) fails because A2 is 0.29999999999999999 due to floating-point representation.
- SUM of a column does not equal a manually entered expected total because the displayed rounded values do not reflect the underlying stored precision.
- Currency or financial calculations carried out with many intermediate decimal steps accumulate fractional cent errors.
Example fix
Broken
=IF(0.1+0.2=0.3,"OK","Error")Fixed
=IF(ROUND(0.1+0.2,10)=ROUND(0.3,10),"OK","Error")0.1+0.2 evaluates to 0.30000000000000004 in binary floating-point, so the direct equality check returns 'Error'. Rounding both sides to 10 decimal places eliminates the binary residual while preserving any real numeric difference.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Wrap intermediate or final results in ROUND() to control precision: =ROUND(A2+B2,2) rounds to 2 decimal places, which is appropriate for most currency and percentage work.
- 2For comparisons, never check equality to a float directly. Use ROUND on both sides: =IF(ROUND(A2,10)=ROUND(0.3,10),"equal","not equal") — 10 decimal places is enough to eliminate floating-point noise while retaining real differences.
- 3For financial rounding: use ROUND(value,2) throughout. Do not apply ROUND only at the display stage — apply it at each calculation step to prevent accumulation.
- 4In Excel, enable 'Set precision as displayed' (File > Options > Advanced > When calculating this workbook) — this rounds stored values to match displayed decimal places. Use with caution: it permanently changes stored values.
- 5Use ROUNDDOWN() or ROUNDUP() instead of ROUND() where directional rounding is required (e.g., always rounding up for fees, always down for quantities).
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every Rounding and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
My SUM of column A does not match the total I calculated manually — the difference is tiny (e.g., 0.0000001). Is this a bug?
No. It is a floating-point residual from the intermediate additions. Use =ROUND(SUM(A:A),2) to round the sum to the precision you care about. For currency, always round at the reporting stage.
Should I use ROUND or just format the cell to show fewer decimal places?
Format only changes the display — the stored value still has full precision and will be used as-is in further calculations. ROUND changes the stored value. For any calculation that will be used downstream (especially comparisons or totals), use ROUND on the value.
Is there a way to see the full floating-point value stored in a cell?
Format the cell as Number with 15 decimal places. Excel and Google Sheets store up to 15 significant digits. This reveals any binary rounding residuals.