Statistical
Plus-or-Minus (+/-) Tolerance Check & Count Out-of-Spec Values
Classify measurements using an explicit six-decimal comparison policy and count rejected parts from the same classifications.
Choose a measurement precision policy before comparing
This worked example rounds both the absolute deviation and tolerance to six decimal places in the measurement unit. It avoids the small binary subtraction residue in the sample, but it also deliberately treats differences below that resolution as equal. It is not a universal guarantee of engineering acceptance.
Choose precision from the measurement specification and uncertainty requirements. For example, 25.0500004 is accepted here while 25.0500006 is rejected at target 25 and tolerance 0.05. With zero tolerance, sufficiently small deviations can still round to zero.
All measurements, target and tolerance must be numeric. Tolerance must be nonnegative. Missing or invalid rows invalidate the batch count rather than silently disappearing.
| Part ID | Diameter (mm) | ROUND(ABS(B - 25.00), 6) | Status (+/- 0.05 mm Spec) |
|---|---|---|---|
| P-1 | 25.02 mm | 0.02 mm <= 0.05 | In Spec |
| P-2 | 24.94 mm | 0.06 mm > 0.05 (< LSL 24.95) | Reject (Undersize) |
| P-3 | 25.00 mm | 0.00 mm <= 0.05 | In Spec (Nominal) |
| P-4 | 25.08 mm | 0.08 mm > 0.05 (> USL 25.05) | Reject (Oversize) |
| P-5 | 24.97 mm | 0.03 mm <= 0.05 | In Spec |
Classify each part in column C
Enter this formula in C2 and fill down through C6. The $ signs keep the target and tolerance fixed. The prepared workbook already contains these helper formulas.
=IF(OR(NOT(ISNUMBER(B2)),NOT(ISNUMBER($D$2)),NOT(ISNUMBER($D$3))),"Invalid inputs",IF($D$3<0,"Invalid inputs",IF(ROUND(ABS(B2-$D$2),6)<=ROUND($D$3,6),"In Spec","Reject")))Total Count of Out-of-Tolerance Rejects Across Batch
Count the helper statuses so the total uses exactly the same rounding policy as each part. If any status is invalid, fix the input before reporting a batch count.
=IF(COUNTIF(C2:C6,"Invalid inputs")>0,"Invalid inputs",COUNTIF(C2:C6,"Reject"))Try the example
Use the original values or change them to test your understanding.