The HYPGEOM.DIST function in Excel is used to calculate the hypergeometric distribution, which is helpful in determining probabilities in scenarios where you are dealing with a finite population without replacement. This function is particularly useful in situations like quality control, lotteries, or any scenario involving a finite number of successes in a sample.
Here’s the syntax for the HYPGEOM.DIST function:
HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative)
Where:
- sample_s: The number of successes in the sample (the number you are interested in).
- number_sample: The size of the sample being drawn from the population.
- population_s: The number of successes in the entire population.
- number_pop: The size of the total population.
- 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:
Suppose you want to find the probability of drawing exactly 2 defective items out of a sample of 10 items from a batch of 100 items, where there are 20 defective items in total.
- sample_s = 2 (you want the probability of 2 defective items in the sample)
- number_sample = 10 (you are drawing a sample of 10 items)
- population_s = 20 (there are 20 defective items in the total population)
- number_pop = 100 (the total population size)
- cumulative = FALSE (you want the exact probability, not the cumulative probability)
The formula you would use in Excel is:
=HYPGEOM.DIST(2, 10, 20, 100, FALSE)
This will return the probability of drawing exactly 2 defective items in your sample of 10.
Alternatively, if you wanted to find the cumulative probability of drawing up to 2 defective items, you would set cumulative to TRUE:
=HYPGEOM.DIST(2, 10, 20, 100, TRUE)
This would give you the probability of drawing 0, 1, or 2 defective items in your sample of 10.
Unlock Your Potential
Excel
Access
Power BI
