Within-Tolerance Check

Excel Formulas › Auditing & Error-Proofing

All versionsABS

Two figures rarely match to the penny — rounding, FX, or estimates create small gaps. A tolerance check passes when the difference is within an allowed amount, so trivial differences don’t trigger false alarms.

Quick formula: is the difference within tolerance?
=ABS(value_a - value_b) <= tolerance
The absolute difference must be no more than the allowed tolerance; bigger gaps fail.

Functions used (tap for the full reference guide):

ABS

The example

$1,000.02 vs $1,000.00, tol $0.05.

AB
1CheckResult
2Within $0.05?TRUE

The formula

The formula:

=ABS(value_a - value_b) <= tolerance // |difference| ≤ tolerance

How it works

How it works:

  1. ABS(a - b) is the size of the gap, ignoring which is larger.
  2. Compare it to the tolerance — within it passes, beyond it fails.
  3. Use an absolute tolerance (e.g. $0.05) or a relative one (a percent of the value).
  4. Wrap in IF to return Pass/Investigate, or feed a conditional-format rule.

Relative tolerance scales with size. A $0.05 absolute tolerance is right for small amounts but too tight for millions. Use ABS(a-b) <= rate * MAX(ABS(a),ABS(b)) for a percentage tolerance, so a 0.1% allowance flexes with the numbers. Pick absolute for cash reconciliation, relative for estimates and forecasts.

Try it: interactive demo

Live demo

Two values and a tolerance.

Within tolerance?

Variations

Relative tolerance

Percent of value:

=ABS(a-b) <= rate * MAX(ABS(a), ABS(b))

Pass/investigate

Readable:

=IF(ABS(a-b)<=tol, "Pass", "Investigate")

The gap itself

How far:

=ABS(value_a - value_b)

Pitfalls & errors

Use ABS. Without it the sign of the difference matters — you want magnitude.

Absolute vs relative. A fixed tolerance is too tight for large numbers; scale it for big values.

Set tolerance deliberately. Too loose hides real errors; too tight cries wolf.

Practice workbook

📊
Download the free Within-Tolerance Check practice workbook
A tolerance sheet with the relative, pass/investigate, and gap variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I check if two values are within tolerance in Excel?
Use =ABS(value_a - value_b) <= tolerance. The absolute gap must not exceed the allowed amount.
How do I use a percentage tolerance?
Scale it: =ABS(a-b) <= rate * MAX(ABS(a), ABS(b)) so the allowance grows with the numbers.
Why use ABS?
You care about the size of the difference, not which value is larger, so take the absolute value.

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: Compare with tolerance · Sum absolute values · Cross-foot check

Function references: ABS