When modeling probabilities in quality inspection, lot acceptance testing, card games, or lottery scenarios, analysts frequently evaluate finite populations where items are sampled without replacement. While binomial distributions assume independent trials with constant probability (sampling with replacement), drawing items from a finite batch fundamentally changes the probability of subsequent draws as the population depletes.
To accurately evaluate finite sampling without replacement, statisticians employ the hypergeometric distribution. In Google Sheets, this mathematical model is executed natively via the HYPGEOM.DIST function. This guide explains its parameters, differences from binomial modeling, and practical manufacturing quality control implementation.
Understanding Hypergeometric vs Binomial Distributions
The distinction between sampling methods is critical for probability accuracy:
- Binomial Sampling (With Replacement): Each selected item is returned to the pool prior to the next draw. Trials remain independent, and the probability of selecting a target item remains static throughout.
- Hypergeometric Sampling (Without Replacement): Each selected item is permanently removed from the population. Success probabilities change dynamically with every draw, which is typical of physical quality control batch sampling.
Syntax and Parameter Requirements
The syntax for HYPGEOM.DIST in Google Sheets requires five parameters:
=HYPGEOM.DIST(num_successes, num_draws, successes_in_pop, pop_size, cumulative)Parameter Definitions
- num_successes: The observed or targeted number of successful items in the sample (must be >= 0 and <= num_draws).
- num_draws: The total number of items drawn in the sample (sample size).
- successes_in_pop: The total number of successful or defective items in the overall population.
- pop_size: The total size of the finite population (must be >= 0 and >= successes_in_pop).
- cumulative: Logical
TRUEfor cumulative distribution (probability of <= num_successes);FALSEfor exact probability density.
Step-by-Step Quality Control Example
Suppose a manufacturing warehouse receives a batch of 100 industrial microchips (population size). Quality records suggest that 10 defective chips exist within this shipment. A quality inspector randomly pulls 15 chips from the crate for destructive laboratory testing.
Structure your Google Sheets analysis table as follows:
A1: Parameter | B1: Value
A2: Population Size (N) | B2: 100
A3: Defectives in Population (M) | B3: 10
A4: Sample Size (n) | B4: 15
A5: Target Defectives in Sample | B5: 2
A6: Exact Probability (2 defects) | B6: =HYPGEOM.DIST(B5, B4, B3, B2, FALSE)
A7: Cumulative (<= 2 defects) | B7: =HYPGEOM.DIST(B5, B4, B3, B2, TRUE)
A8: Rejection Risk (> 2 defects) | B8: =1 - B7Google Sheets calculates the results:
- Exact Probability of exactly 2 defects (B6): 28.84%
- Cumulative Probability of 2 or fewer defects (B7): 83.33%
- Probability of finding more than 2 defects (B8): 16.67%
Distribution Modeling Comparison Table
| Distribution Type | Google Sheets Function | Replacement Rule | Population Requirement |
|---|---|---|---|
| Hypergeometric | HYPGEOM.DIST |
Without replacement | Finite, known population size |
| Binomial | BINOM.DIST |
With replacement | Infinite or independent trials |
| Negative Binomial | NEGBINOM.DIST |
Independent trials until r successes | Unbounded trial count |
Troubleshooting Common Errors
- #NUM! Error: Occurs when sample size exceeds population size, when successes exceed sample size, or when negative numbers are provided.
- #VALUE! Error: Occurs if text characters or unparsed strings are passed into parameter arguments.