# How to check if a value is within a tolerance in Excel

Compare the distance from the target with the tolerance: =IF(ABS(B2-$F$1)<=$F$2,"Pass","Fail").

**Excel:** `=IF(ABS(B2-$F$1)<=$F$2,"Pass","Fail")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Part | Measured mm | QC |  | Target | 25 |
| B-01 | 25.03 |  |  | Tolerance | 0.05 |
| B-02 | 24.94 |  |  |  |  |
| B-03 | 25.04 |  |  |  |  |
| B-04 | 25.07 |  |  |  |  |
| B-05 | 24.98 |  |  |  |  |
| B-06 | 24.96 |  |  |  |  |

Formula in C2, filled to C7: `=IF(ABS(B2-$F$1)<=$F$2,"Pass","Fail")`

Result: Pass; Fail; Pass; Fail; Pass; Pass

## How it works

`ABS(B2-$F$1)` is the distance from the target whichever side the measurement falls on. If that distance is at most the tolerance, the part passes. B-02 is 0.06 under the target and fails.

## Other ways

- `=IF(AND(B2>=$F$1-$F$2,B2<=$F$1+$F$2),"Pass","Fail")`: The same window written as a lower and an upper limit.
- `=IF(ROUND(ABS(B2-$F$1),4)<=$F$2,"Pass","Fail")`: Rounds the deviation, so a value exactly on the limit isn't failed by a floating-point leftover.

## Common mistakes

- Values exactly on the limit can fail because decimals aren't stored exactly: 25.05-25 is 0.0500000000000007. Round the difference, as in the second alternative.
- A percentage tolerance needs the target in it: `ABS(B2-$F$1)<=$F$1*2%`.

## Questions

**How do I show how far off each part is?**

`=B2-$F$1` gives the signed deviation.

**How do I count the passes?**

`=COUNTIF(C2:C7,"Pass")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/check-if-value-is-within-tolerance