The HYPGEOM.DIST function in Excel calculates the probability of a specific number of successes in a sequence of draws from a finite population without replacement. This statistical function is instrumental in scenarios where you want to assess the likelihood of obtaining a certain outcome in a limited group, making it invaluable in fields such as quality control and biological studies.

Syntax

HYPGEOM.DIST(x, n, M, N, cumulative)

• x: The number of successes in the sample.
• n: The number of draws or sample size.
• M: The total number of successes in the population.
• N: The population size or total number of items.
• cumulative: A logical value that determines the form of the function. If TRUE, HYPGEOM.DIST returns the cumulative distribution function; if FALSE, it returns the probability mass function.

Example #1

=HYPGEOM.DIST(5, 10, 20, 50, FALSE)

This function calculates the probability of getting exactly 5 successes (like selecting defective items) from 10 draws, where there are 20 successes in a total of 50 items. The result might be approximately 0.179.

Example #2

=HYPGEOM.DIST(2, 5, 10, 30, TRUE)

In this case, the function evaluates the cumulative probability of obtaining 2 or fewer successes in 5 draws, with 10 successes within a population of 30 items. The outcome may be around 0.244.

Example #3

=HYPGEOM.DIST(3, 6, 15, 40, FALSE)

This formula checks the likelihood of getting exactly 3 successes from 6 draws, given that there are 15 successes among a total of 40 items. The result could be roughly 0.211.

Error handling

• NUM!: Indicates that at least one argument is out of the designated range; ensure that x, n, M, and N are appropriate for the hypergeometric distribution.
• VALUE!: Occurs when one or more parameters are of the wrong type; check that all inputs are numeric.
• DIV/0!: This error signifies that the function is attempting to divide by zero, which may happen if N (the total population size) is zero.

Conclusion

The HYPGEOM.DIST function is a powerful tool for analyzing scenarios involving draws from a finite population, particularly when outcomes are not replaced. By understanding its syntax and parameters, users can effectively model various probabilistic situations, enabling better decision-making based on statistical insights.