Math
Fix floating-point errors: why 1.33 - 1.3 is not 0.03 and how ROUND solves it
Prevent false reconciliation breaks caused by IEEE-754 binary decimals using explicit ROUND(..., 2) or ABS tolerance checks.
Why clean decimals produce tiny 0.00000000000000003 remainders
Both Microsoft Excel and browser spreadsheets use IEEE-754 double-precision binary floating-point numbers. Base-10 fractions like 0.1, 0.3, and 1.33 have repeating representations in binary (just like 1/3 = 0.3333... in base 10).
Subtracting two nearby numbers—such as 1.33 - 1.3—exposes that microscopic binary residue (0.030000000000000027), causing =(A2 - B2) = 0.03 to return FALSE and IF((Debit - Credit)=0, "Balanced", "Out of balance") to raise false alarms!
| Billed (A) | Paid (B) | ExpectedBalance (C) | Raw (A-B)=C | ROUND(A-B, 2)=C |
|---|---|---|---|---|
| 1.33 | 1.3 | 0.03 | FALSE | TRUE |
Wrap currency differences in ROUND(..., 2) before comparing
For this example the intended precision is cents, so round both sides of a monetary comparison to two decimals. Choose precision according to the currency and calculation rules; rounding intermediate values can change totals.
=ROUND(A2 - B2, 2) = C2Alternative: ABS epsilon tolerance check
In scientific or engineering calculations where fixed decimal places vary, test ABS((A2 - B2) - C2) < 1E-9.
Choose an absolute or relative tolerance suited to the units and magnitude. A fixed 1E-9 is illustrative, not a universal engineering threshold.
=ABS((A2 - B2) - C2) < 0.000000001Try the example
Use the original values or change them to test your understanding.