Compare Numbers with a Tolerance

Excel Formulas › Logical

All versionsABS

Two calculated numbers that should be equal sometimes differ by a tiny rounding amount. Compare them within a tolerance — ABS(difference) ≤ epsilon — instead of demanding exact equality.

Quick formula: are A2 and B2 equal within 0.01?
=ABS(A2 - B2) <= 0.01
The absolute difference being at or under the tolerance means “close enough” — TRUE even if they aren’t bit-for-bit equal.

Functions used (tap for the full reference guide):

ABSROUND

The example

Near-equal values pass the tolerance test.

AB
1CompareEqual±0.01?
210.001 vs 10.000TRUE
310.5 vs 10.0FALSE

The formula

Close enough, not exactly equal:

=ABS(A2 - B2) <= 0.01 // within tolerance

How it works

Test the size of the difference:

  1. A2 − B2 is the difference; ABS ignores its sign.
  2. Compare to a small tolerance (epsilon): <= 0.01 means within a cent, <= 0.000001 for tighter math.
  3. TRUE means “equal for practical purposes,” even when floating-point math leaves a microscopic difference.
  4. Alternative: compare after rounding both — ROUND(A2,2)=ROUND(B2,2).

Why exact equality fails: 0.1+0.2 isn’t exactly 0.3 in binary floating point, so =A2=B2 can be FALSE for values that look identical. A tolerance comparison (or rounding) is the robust fix.

Try it: interactive demo

Live demo

Two values + a tolerance.

Equal?

Variations

Round then compare

To 2 decimals:

=ROUND(A2,2)=ROUND(B2,2)

Percent tolerance

Within 1%:

=ABS(A2-B2)/B2 <= 0.01

Flag mismatches

Audit:

=IF(ABS(A2-B2)>0.01, "Check", "")

Pitfalls & errors

Floating point isn’t exact. Don’t use =A2=B2 on calculated decimals — tiny binary errors cause false negatives.

Pick the right epsilon. Too tight and rounding noise fails; too loose and real differences slip through.

Percent vs absolute. A fixed tolerance suits similar magnitudes; use a relative (percent) tolerance across very different scales.

Practice workbook

📊
Download the free Compare Numbers with a Tolerance practice workbook
A tolerance-compare sheet with the round, percent, and flag variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I compare two numbers within a tolerance in Excel?
Use =ABS(A2 - B2) <= tolerance, e.g. <= 0.01. It returns TRUE when the values are close enough, even if not exactly equal.
Why does =A2=B2 return FALSE for equal-looking decimals?
Floating-point math stores some decimals imprecisely (0.1+0.2 isn't exactly 0.3). Compare with a tolerance or round both first.
How do I use a percentage tolerance?
Divide by the base: =ABS(A2-B2)/B2 <= 0.01 tests within 1%.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Round to nearest · Compare two columns · Nth root & powers

Function references: ABS · ROUND