Floating Point Errors: Troubleshooting why (1.2 – 1.1) doesn’t equal 0.1 in Excel.

If you type =(1.2 - 1.1) = 0.1 into an Excel cell, the formula returns FALSE. If you expand the decimal display of =1.2 - 1.1 to sixteen decimal places, you will see 0.0999999999999999 instead of 0.1. This is not a software bug or a broken calculation engine. It is an inherent limitation of binary computing known as a floating-point error. Understanding why this happens—and how to isolate it—is critical for preventing broken logical tests, failing lookups, and faulty audit controls.

Quick Answer

Excel stores numbers using binary floating-point math (IEEE 754), which cannot cleanly represent base-10 fractions like 0.1. Never use exact equality (A1 = B1) on calculated decimals. Instead, wrap calculations in =ROUND(formula, decimal_places) or evaluate equality using a tiny tolerance threshold: =ABS((1.2 - 1.1) - 0.1) < 1E-9.

---

The Root Cause: Binary Floating-Point Arithmetic (IEEE 754)

Computers do not calculate in base-10 (decimal). They calculate in base-2 (binary).

In decimal math, fractions whose denominators have prime factors other than 2 or 5 cannot be written as clean, terminating numbers. For example, $1/3$ becomes a repeating decimal: $0.333333…$ Because paper or screens have finite space, you must stop writing at some point, introducing a slight rounding error.

The exact same thing happens in binary, but with far more numbers. In binary, a fraction can only terminate cleanly if its denominator is a power of 2 ($1/2, 1/4, 1/8, 1/16$, and so on). Common decimal fractions like $0.1$ ($1/10$) or $0.2$ ($1/5$) convert to infinite repeating binary sequences:

$\frac{1}{10} = 0.0001100110011…_2$

Excel adheres to the industry-standard IEEE 754 specification for double-precision floating-point numbers. This standard allocates exactly 64 bits of storage per number:

• 1 bit for the sign (positive or negative)
• 11 bits for the exponent
• 52 bits for the mantissa (storing roughly 15 to 17 significant decimal digits)

Because the binary sequence for 0.1 is infinite, Excel cuts it off at bit 52. When you subtract 1.1 from 1.2, the tiny truncation differences in both numbers collide, leaving an infinitesimal residue: 0.0999999999999999 instead of 0.1000000000000000.

---

Where Floating-Point Residuals Break Models

A difference of 0.0000000000000001 seems inconsequential, but it silently breaks three common Excel structures:

1. Logical IF Statements and Switch Flags

=IF(A1 - B1 = 0.1, "Release Escrow", "Hold Funds")

If A1 is 1.2 and B1 is 1.1, the logical test evaluates to FALSE. Escrow is held indefinitely even though the human reviewer sees $0.10 on the worksheet.

2. Exact-Match Lookups (VLOOKUP, XLOOKUP, MATCH)

If you calculate an index value of 0.1 and run an exact lookup (match_type = 0) against a table of milestones formatted as decimals, Excel will return #N/A. The lookup value 0.0999999999999999 does not match the static table value 0.1000000000000000.

3. Zero-Balance Check-Sums

In balance sheet modeling, checking whether total debits equal total credits often returns residual fractions like -8.32667E-17 rather than 0.00. An automated audit test built to flag non-zero balances will constantly throw false positives. For broader reconciliation strategies, see Check-Sum Failures: Building “Error Dashboards” that flag when a model is out of balance..

---

Diagnostic Step: Exposing the Residual Bits

If you suspect a formula is failing due to floating-point drift:

1. Expand Decimal Places: Select the problematic cell, open Format Cells (Ctrl + 1), select Number, and increase decimal places to 16 or 17. If the value contains trailing nines or a trailing non-zero digit after 14 zeros, floating-point drift is present.
2. Formula Bar vs. Cell Display: Excel’s grid presentation layer often masks floating-point artifacts by rounding display outputs to 15 digits. If a cell displays 0.10 but fails an equality test, enter this diagnostic test in an empty cell:
   Format the result as Scientific. If it returns a value like 1.38778E-16 instead of 0.00E+00, the underlying number carries floating-point noise. =C1 - 0.1

---

How to Fix the Problem

Do not turn on “Set precision as displayed” to fix floating-point math. That setting permanently destroys underlying data across the entire workbook. Review the catastrophic risks of that setting in Precision as Displayed: Why 0.01 differences in Balance Sheets occur (and how to fix them)..

Instead, use one of the two reliable methods below.

How are you testing the number?
   ├── Exact Equality / Logical Test (IF, OR, AND) ──> Method 1: Delta Threshold (Epsilon)
   └── Cell Lookup, Balance Sheet, Display Match ────> Method 2: Explicit ROUND()

Method 1: Use an Epsilon Tolerance for Logical Tests

Rather than testing if two calculated numbers are perfectly identical, test if their absolute difference is smaller than a negligible threshold (often called an epsilon):

=IF(ABS((1.2 - 1.1) - 0.1) < 1E-9, "Balanced", "Unbalanced")

Here, 1E-9 (0.000000001) serves as the tolerance window. Any floating-point drift in the 15th or 16th decimal place is ignored, while true accounting differences are caught immediately.

Method 2: Apply Explicit Formula-Level Rounding

If the value feeds subsequent formulas, lookups, or financial statements, truncate the floating-point noise at the calculation point using =ROUND():

=ROUND(1.2 - 1.1, 2)

Now, =ROUND(1.2 - 1.1, 2) = 0.1 evaluates to TRUE. The rounding eliminates the trailing binary fraction before it can contaminate downstream financial logic.