Statistical
Hypergeometric probability formula: exact sampling without replacement via FACT
Calculate exact finite-lot inspection probabilities P(X = k) = C(K, k) * C(N - K, n - k) / C(N, n) and finite-population variance using FACT and LET.
Why Binomial fails on small audit lots—and how Hypergeometric counts exact draws!
For simple random sampling without replacement, with every sample of the given size equally likely, the hypergeometric distribution counts successes in a finite population. The example uses population 20, four defective items and five draws.
This teaching implementation expands combinations through factorials and is deliberately limited to population sizes 1–170 to avoid floating-point factorial overflow. It is not a universal large-population implementation. Probabilities shown to four decimals are rounded, not exact rational numbers. Impossible integer hit counts return zero; invalid population parameters return Invalid inputs.
| Lot Audit Parameter | Cell | Value | Exact Result |
|---|---|---|---|
| Lot Size (popN) | B2 | 20 | C(20, 5) = 15,504 total samples |
| Defectives in Lot (defK) | B3 | 4 | Non-defective = 16 |
| Sample Drawn (drawN) | B4 | 5 | Expected defects E[X] = 1.00 |
| Defectives Found (hitK) | B5 | 2 | P(X = 2) = 3,360 / 15,504 = 0.2167 |
Exact Hypergeometric PMF P(X = k) via FACT & LET
Use distinct LET variable names. Population, defect count, draw count and target hits must be integers. Require 1<=population<=170, 0<=defects<=population and 0<=draws<=population. The feasible target range is MAX(0,draws-(population-defects)) through MIN(defects,draws).
=IF(COUNT(B2:B5)<>4,"Invalid inputs",IF(OR(B2<1,B2>170,B3<0,B3>B2,B4<0,B4>B2,B2<>INT(B2),B3<>INT(B3),B4<>INT(B4),B5<>INT(B5)),"Invalid inputs",IF(OR(B5<MAX(0,B4-(B2-B3)),B5>MIN(B3,B4)),0,LET(popN, B2, defK, B3, drawN, B4, hitK, B5, cHit, FACT(defK) / (FACT(hitK) * FACT(defK - hitK)), cMiss, FACT(popN - defK) / (FACT(drawN - hitK) * FACT(popN - defK - (drawN - hitK))), cAll, FACT(popN) / (FACT(drawN) * FACT(popN - drawN)), ROUND((cHit * cMiss) / cAll, 4)))))Finite-population variance n * (K/N) * (1 - K/N) * (N - n)/(N - 1)
Notice the finite-population correction factor (N - n) / (N - 1) = 15/19 shrinks variance below Binomial n*p*(1-p) = 0.80 down to 12/19 = 0.6316!
=IF(COUNT(B2:B5)<>4,"Invalid inputs",IF(OR(B2<1,B2>170,B3<0,B3>B2,B4<0,B4>B2,B2<>INT(B2),B3<>INT(B3),B4<>INT(B4),B5<>INT(B5)),"Invalid inputs",IF(B2=1,0,ROUND(B4 * (B3 / B2) * (1 - B3 / B2) * ((B2 - B4) / (B2 - 1)), 4))))Try the example
Use the original values or change them to test your understanding.