How to Use the HYPGEOM.DIST Function in Microsoft Excel for Hypergeometric Probability

Statistical sampling and quality assurance auditing frequently encounter scenarios where items are sampled from a finite population without replacement. When sampling without replacement, the probability of selecting an item changes with each draw, meaning the binomial distribution (which assumes independent trials with replacement) no longer applies. To model these probabilities accurately, Microsoft Excel provides the HYPGEOM.DIST function. Understanding how to construct and interpret this function is essential for manufacturing quality control, compliance sampling, risk management, and lottery probability modelling.

Understanding the Hypergeometric Distribution

A hypergeometric experiment exhibits four fundamental criteria:

1. The population consists of a fixed, finite number of items (N).
2. Each item in the population can be classified into one of two mutually exclusive categories (e.g., Success or Failure, Defective or Non-defective).
3. A specific sample size (n) is drawn completely at random.
4. The sample is drawn without replacement, meaning individual trial probabilities shift as items are drawn.

Syntax and Arguments of HYPGEOM.DIST

The standard syntax for the function in modern Microsoft Excel is:

=HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative)

The function takes five arguments:

• sample_s (Required): The number of successes in the sample. Must be an integer greater than or equal to 0, and cannot exceed number_sample or population_s.
• number_sample (Required): The total size of the sample drawn from the population. Must be greater than 0 and less than or equal to number_pop.
• population_s (Required): The total number of successes in the overall population.
• number_pop (Required): The total population size.
• cumulative (Required): A logical value (TRUE or FALSE) determining the form of the function:
  • FALSE: Calculates the Probability Mass Function (PMF)—the exact probability of observing precisely sample_s successes.
  • TRUE: Calculates the Cumulative Distribution Function (CDF)—the cumulative probability of observing up to and including sample_s successes.

Practical Example 1: Quality Control and Defect Inspection

Imagine a manufacturing shipment containing a total batch of 100 electronic components (number_pop = 100). Historical quality checks reveal that exactly 10 components in the batch are defective (population_s = 10). An inspector selects a random sample of 12 components without replacement (number_sample = 12).

Calculating the Exact Probability of Finding Exactly 2 Defective Items

To calculate the probability that the sample contains exactly two defective items, set cumulative to FALSE:

=HYPGEOM.DIST(2, 12, 10, 100, FALSE)

Excel returns approximately 0.2307 (or a 23.07% chance).

Calculating the Probability of Finding At Most 2 Defective Items

To evaluate the cumulative risk of finding 0, 1, or 2 defective components in the test batch, set cumulative to TRUE:

=HYPGEOM.DIST(2, 12, 10, 100, TRUE)

Excel returns approximately 0.9022 (a 90.22% probability that 2 or fewer defects will be encountered).

Calculating the Probability of Finding More Than 2 Defective Items

To determine the complementary probability of encountering a severe failure rate (more than 2 defectives in the sample):

=1 - HYPGEOM.DIST(2, 12, 10, 100, TRUE)

Excel returns 0.0978 (approximately 9.78%).

Practical Example 2: Committee Selection Probabilities

Consider an enterprise department with 40 employees (number_pop = 40), of whom 15 are senior consultants (population_s = 15). A strategic steering committee of 6 employees is chosen completely at random (number_sample = 6).

To find the probability that the committee contains at least 3 senior consultants:

=1 - HYPGEOM.DIST(2, 6, 15, 40, TRUE)

This formula computes the total probability minus the cumulative probability of selecting 0, 1, or 2 senior consultants.

Common Errors and Troubleshooting

• #NUM! Error: Occurs if any argument is negative, if sample_s > number_sample, if sample_s > population_s, or if number_sample > number_pop. Ensure logical boundary constraints are respected.
• #VALUE! Error: Occurs when any supplied argument contains non-numeric text values or unrecognised characters.
• Deprecated Function Warning: Older Excel workbooks might display HYPGEOMDIST (without the period). While supported for backwards compatibility, always use the modern HYPGEOM.DIST for improved precision and clarity.

Mastering HYPGEOM.DIST equips financial analysts, quality assurance engineers, and data professionals to conduct rigorous sampling assessments and model finite-population risks with complete mathematical accuracy.