Statistical Analysis
Binomial Acceptance Sampling & Critical Defect Cutoff (BINOMDIST & CRITBINOM)
Calculate exact binomial defect probabilities, cumulative lot-acceptance curves, producer’s risk, and the smallest critical defect threshold (CRITBINOM) using exact factorial combinatorics.
How CRITBINOM determines incoming quality-control acceptance numbers
When inspecting a random sample of n units from a supplier lot with Acceptable Quality Limit (AQL) defect rate p, the number of defective units k follows a Binomial(n, p) distribution with exact probability P(X = k) = (n! / (k! * (n - k)!)) * p^k * (1 - p)^(n - k).
CRITBINOM(n, p, target_cdf) finds the smallest integer acceptance cutoff c such that the cumulative probability P(X <= c) is at least target_cdf (for example, 0.90 or 0.95), keeping Producer’s Risk alpha = 1 - P(X <= c) below the allowed false-rejection rate.
| Defects k (n=10, p=0.10) | Exact PMF P(X = k) | Cumulative P(X <= k) | Meets >= 0.90 Target? |
|---|---|---|---|
| 0 | 0.3487 | 0.3487 | No |
| 1 | 0.3874 | 0.7361 | No |
| 2 (CRITBINOM cutoff) | 0.1937 | 0.9298 | Yes (Producer risk = 0.0702) |
| 3 | 0.0574 | 0.9872 | Yes |
Exact Binomial PMF Formula
Compute combinations n! / (k! * (n - k)!) via FACT and multiply by p^k * (1 - p)^(n - k).
=ROUND((FACT($B$2) / (FACT(D2) * FACT($B$2 - D2))) * ($B$3 ^ D2) * ((1 - $B$3) ^ ($B$2 - D2)), 4)Critical Acceptance Cutoff (CRITBINOM) via MINIFS on Cumulative Column
Accumulate exact unrounded PMF terms in a running CDF column and extract the minimum defect count k whose cumulative probability clears target_cdf ($B$4).
=MINIFS(D2:D6, F2:F6, ">=" & B4)Try the example
Use the original values or change them to test your understanding.