Statistical
Filter and count values within or out of tolerance (Target ± Spec Band)
Extract passing parts inside [Target - Tol, Target + Tol] and count out-of-spec defects across manufacturing or audit logs.
Spec-limit interval math for quality control and cash-reconciliation tolerances
The example uses inclusive bounds target-tolerance through target+tolerance. A numeric measurement exactly on either boundary is counted as within the band.
Use a nonnegative numeric tolerance, a numeric target and comparable numeric measurements in the same units. Missing or text readings must be audited separately; they are not valid passing or failing measurements.
The formulas below require all five readings to be numeric. They demonstrate an interval check, not process capability, measurement uncertainty analysis or a complete acceptance policy.
For computed readings near a boundary, define the required measurement precision explicitly. Avoid arbitrary rounding that could change the acceptance rule.
| Batch ID (A) | Measured mm (B) | Spec Band [49.85, 50.15] | QC Disposition |
|---|---|---|---|
| LOT-01 | 50.08 mm | Inside ±0.15 mm | PASS (In-Spec) |
| LOT-02 | 49.85 mm | Exact Lower Spec Limit | PASS (Boundary inclusive) |
| LOT-03 | 50.22 mm | +0.22 mm (> +0.15) | REJECT (Over Upper Spec) |
| LOT-04 | 49.79 mm | -0.21 mm (< -0.15) | REJECT (Under Lower Spec) |
| LOT-05 | 49.96 mm | Inside ±0.15 mm | PASS (In-Spec) |
Count readings within the inclusive tolerance
Multiplying >= LowerSpec by <= UpperSpec acts as element-wise AND across the measurement column.
=IF(OR(NOT(ISNUMBER(E2)),NOT(ISNUMBER(F2)),F2<0,COUNT(B2:B6)<>5),"Check inputs",SUMPRODUCT((B2:B6>=E2-F2)*(B2:B6<=E2+F2)))Count out-of-tolerance defects (strictly below LSL or strictly above USL)
Since a single measurement cannot simultaneously be < LSL and > USL, the two flags are mutually exclusive and sum cleanly in SUMPRODUCT.
=IF(OR(NOT(ISNUMBER(E2)),NOT(ISNUMBER(F2)),F2<0,COUNT(B2:B6)<>5),"Check inputs",SUMPRODUCT((B2:B6 < E2 - F2) + (B2:B6 > E2 + F2)))List matching batch IDs
FILTER keeps the selected IDs; TEXTJOIN makes a compact label.
=IF(OR(NOT(ISNUMBER(E2)),NOT(ISNUMBER(F2)),F2<0,COUNT(B2:B6)<>5),"Check inputs",TEXTJOIN(", ",TRUE,FILTER(A2:A6,(B2:B6>=E2-F2)*(B2:B6<=E2+F2),"No in-band readings")))Try the example
Use the original values or change them to test your understanding.