Statistical modelling often requires determining the exact probability of drawing a specific number of successes from a finite population without replacing selected items between draws. While the binomial distribution assumes an infinite population or replacement where probability remains constant across trials, sampling without replacement alters the probability with each successive draw. The HYPGEOM.DIST function in Google Sheets calculates hypergeometric probability distributions accurately for manufacturing batch inspection, audit sampling, and risk analysis.
Understanding Hypergeometric Distribution vs Binomial Distribution
In standard quality control testing, drawing a defective component from a small production batch reduces both the total remaining batch size and the count of remaining defective items. Because each draw is mathematically dependent on prior outcomes, applying BINOM.DIST introduces skew and overestimates variance. HYPGEOM.DIST accounts for this shifting probability space across finite samples.
A classic example involves drawing an audit sample from a closed ledger of invoices, or drawing cards from a deck without replacement.
Syntax and Argument Structure of HYPGEOM.DIST
The HYPGEOM.DIST function in Google Sheets accepts five parameters:
=HYPGEOM.DIST(num_successes, num_draws, successes_in_pop, pop_size, cumulative)- num_successes: The observed or target number of successes in the sample drawn (sample successes, k).
- num_draws: The total number of items drawn in the sample (sample size, n).
- successes_in_pop: The total number of items possessing the success characteristic in the population (population successes, K).
- pop_size: The total number of items in the entire finite population (population size, N).
- cumulative: A logical boolean flag (
TRUEorFALSE):FALSE: Calculates the Probability Mass Function (PMF), representing the exact probability of obtaining preciselynum_successes.TRUE: Calculates the Cumulative Distribution Function (CDF), representing the probability of obtaining at mostnum_successes(from 0 up tok).
Step-by-Step Implementation Examples
Consider a manufacturing scenario: A shipment contains a batch of 100 electronic sensors (population size, N = 100). Historical testing reveals that exactly 10 sensors in this shipment are defective (population successes, K = 10). Quality assurance selects a random sample of 15 sensors without replacement (sample size, n = 15).
Calculating Exact Probability (PMF)
To calculate the probability that the inspection sample contains exactly 2 defective sensors, set the cumulative parameter to FALSE:
=HYPGEOM.DIST(2, 15, 10, 100, FALSE)Google Sheets evaluates the hypergeometric combinatorial formula and returns approximately 0.2974 (or 29.74%).
Calculating Cumulative Probability (CDF)
To determine the probability that the sample contains at most 2 defective sensors (i.e., 0, 1, or 2 defects), set the cumulative flag to TRUE:
=HYPGEOM.DIST(2, 15, 10, 100, TRUE)This yields approximately 0.8258 (or 82.58%), representing the sum of probabilities for k = 0, k = 1, and k = 2.
Calculating Tail Probabilities (Exceeding a Threshold)
To calculate the likelihood of encountering more than 2 defects in the sample, subtract the cumulative CDF value from 1:
=1 - HYPGEOM.DIST(2, 15, 10, 100, TRUE)This reveals an approximate 17.42% probability that a batch sample will fail acceptance criteria requiring no more than 2 defects.
HYPGEOM.DIST Parameter Constraints and Error Handling
The mathematical domain of the hypergeometric distribution enforces strict constraints. Violating these bounds results in a #NUM! or #VALUE! error in Google Sheets:
| Constraint Rule | Mathematical Rationale | Resulting Error |
|---|---|---|
num_successes < 0 or non-integer |
Cannot observe negative successes; non-integers are truncated | #NUM! |
num_successes > num_draws |
Sample successes cannot exceed total sample draws | #NUM! |
num_successes > successes_in_pop |
Sample successes cannot exceed total population successes | #NUM! |
num_draws > pop_size |
Cannot draw more items than exist in finite population | #NUM! |
successes_in_pop > pop_size |
Sub-group count cannot exceed total population size | #NUM! |
| Non-numeric argument inputs | Parameters must resolve to valid numerical values | #VALUE! |
When constructing dynamic dashboards in Google Sheets, wrap your formula in IFERROR or validate inputs using ISBETWEEN to protect formulas against out-of-range user entries.