The HYPGEOMDIST function in Excel is used to calculate the probability of getting a certain number of successes in a specified number of draws, without replacement, from a finite population. This function is particularly useful in scenarios such as quality testing and statistical sampling where the outcomes are not independent due to the nature of sampling without replacement.

Syntax

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

• sample_s: The number of successful outcomes in the sample.
• number_sample: The total number of draws or sample size.
• population_s: The total number of successful outcomes in the population.
• number_pop: The population size or total number of items.
• cumulative: A logical value indicating whether to return the cumulative distribution function (TRUE) or the probability mass function (FALSE).

Example #1

HYPGEOM.DIST(2, 4, 10, 20, FALSE)

This function calculates the probability of getting exactly 2 successful draws out of 4 from a population of 20 items, where there are 10 successful items. The result would be approximately 0.22.

Example #2

HYPGEOM.DIST(3, 5, 15, 30, TRUE)

This function computes the cumulative probability of getting 3 or fewer successful outcomes when drawing 5 times from a population of 30 with 15 successes. The computed value would be around 0.75.

Example #3

HYPGEOM.DIST(1, 3, 5, 10, FALSE)

This example evaluates the probability of obtaining exactly 1 successful outcome in 3 draws from a population of 10 items, where there are 5 successful ones. The result would be approximately 0.35.

Error handling

• VALUE! This error occurs when one or more arguments have the wrong type, such as text instead of numbers.
• NUM! This error indicates invalid numeric values, such as if ‘sample_s’ is greater than ‘population_s’ or if ‘number_sample’ is negative.
• NAME? This error shows that the function name is not recognized, usually because of a typo.

Conclusion

In summary, the HYPGEOMDIST function in Excel provides a straightforward way to model scenarios involving sampling without replacement. By accurately calculating probabilities, it aids in statistical analysis and decision-making processes where understanding the likelihood of specific outcomes is crucial.