Logical
Check if a value is within a percentage tolerance (±%)
Test symmetric upper and lower tolerance bands in one step with ABS(Measured - Target) / ABS(Target) <= Tolerance.
Collapse upper and lower bounds into one ABS ratio
Instead of writing AND(B2 >= A2*(1-C2), B2 <= A2*(1+C2))—which flips inequality directions if Target is negative—compute relative error ABS(B2 - A2) / ABS(A2) and compare against MaxTolPct.
The example rounds relative error to eight decimal places as an explicit comparison policy. This can admit values slightly outside an exact mathematical boundary; select precision appropriate to the measurement. Require a nonnegative tolerance. At target zero this example accepts only a zero measurement instead of defining a relative percentage error.
| SpecTarget (A) | Measured (B) | MaxTolPct (C) | Relative Diff | Within Spec? |
|---|---|---|---|---|
| 200 | 207 | 0.05 | 3.5% | TRUE |
| 200 | 188 | 0.05 | 6.0% | FALSE |
| 0 | 0 | 0.05 | Undefined; zero-target branch | TRUE |
Zero-safe tolerance check formula
207 against target 200 deviates by 7 / 200 = 3.5% (<= 5% -> TRUE), while 188 deviates by 12 / 200 = 6.0% (> 5% -> FALSE).
=IF(A2=0, B2=0, ROUND(ABS(B2 - A2) / ABS(A2), 8) <= C2)Exact boundary and zero-target checks
At Measured = 210 (+5.0% exact), the formula returns TRUE; at SpecTarget = 0 and Measured = 0, the A4 = 0 branch returns TRUE without dividing by zero.
=IF(A3=0, B3=0, ROUND(ABS(B3 - A3) / ABS(A3), 8) <= C3)Try the example
Use the original values or change them to test your understanding.