📎 This article includes 1 downloadable practice file ↓
Type 0.1 + 0.2 into Python or a browser console and you get 0.30000000000000004. It looks like a bug in a machine that can land rockets. It isn’t a bug. It’s how almost every computer stores decimal numbers, and once you see why, you’ll know exactly where it can bite in Excel.
In this article
Decimals don’t fit in binary
In our decimal system, 1/3 can’t be written exactly: 0.3333… goes on forever, so we round. Computers count in binary (base 2), where only fractions with a power of 2 at the bottom (½, ¼, ⅛…) come out exact. One tenth is not one of them. In binary 0.1 is
0.0001100110011001100110011… repeating forever.
The computer keeps 53 binary digits (the IEEE 754 “double” format used by Excel, Python, JavaScript and nearly everything else) and rounds the rest away. So the stored 0.1 is very slightly more than 0.1, and the stored 0.2 is also slightly off. Add them, round again, and you land one tiny step above the stored value of 0.3. The difference is about 0.000000000000000055.
Why Excel usually “agrees” that it’s 0.3
Excel shows at most 15 significant digits, and it has a deliberate tidy-up: when the final step of a formula is an addition, subtraction or comparison whose result is extremely close to the answer, Excel snaps it. So =0.1+0.2=0.3 returns TRUE and =0.1+0.2-0.3 shows 0. The error is still there in the stored value; it’s just hidden.
Where it shows through in Excel
The snapping only happens at the last step. Wrap the calculation and it disappears:
=1*(0.5-0.4-0.1) → -2.78E-17, not 0
=1*(0.5-0.4-0.1)=0 → FALSE
=MOD(0.3, 0.1) → 0.1 (maths says 0)
=VLOOKUP(0.1+0.2, {0.3}, 1, FALSE) → #N/AIn real workbooks this turns up as:
- A reconciliation “difference” column showing -1.82E-12 instead of 0, so the filter for zero misses it.
- An IF that checks whether debits equal credits says FALSE on a balanced ledger.
- An exact-match lookup on calculated rates (like 0.18 from 18/100 × something) returns #N/A.
- Conditional formatting rule “=0” not firing.
- INT or ROUNDDOWN of a value like 8.2 × 100 occasionally landing one unit low in other tools.
The fixes
1. Round money at each meaningful step. Invoices, taxes and allocations should be rounded to paise when calculated, not only formatted:
=ROUND(B2 * C2, 2)2. Compare with a tolerance, not with = on raw results:
=ABS(A2 - B2) < 0.005 ' within half a paisa
=ROUND(A2 - B2, 2) = 03. Round lookup keys before exact matching: =XLOOKUP(ROUND(x, 4), ROUND(list, 4), result).
4. Work in whole units where it matters: paise instead of rupees, grams instead of kilograms. Integers up to about 9 quadrillion are stored exactly.
Not just Excel
Python, JavaScript, Java, C and most databases show the same thing. Languages offer exact decimal types for money: Python’s decimal.Decimal, SQL’s DECIMAL(12,2), Java’s BigDecimal. Excel has no decimal type, so ROUND is your tool.
Where people go wrong
| Belief | Reality |
|---|---|
| “Excel is broken” | It follows the same IEEE 754 standard as almost all software |
| “Formatting to 2 decimals fixes it” | Formatting hides it; the stored value is unchanged |
| “It’s too small to matter” | It matters whenever you test for exact equality |
Practice
Download the practice workbook below. Predict each TRUE/FALSE before you type the formula, then see what Excel says. Each task has an automatic ✓ check, and the Answers sheet has a working formula if you get stuck.
More oddities: Excel quirks explained.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Floating-point practice workbookNine cases where tiny binary errors show up in Excel, and the ROUND and tolerance fixes, with checks.⬇ XLSX · 13 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.