The HYPGEOM.DIST function calculates the hypergeometric distribution in Excel. This function is useful in statistical analysis to determine the probability of a specific number of successes in a population sampled without replacement.


=HYPGEOM.DIST(Sample_s, Number_sampled, Population_successes, Population, Cumulative)

Sample_s The number of successes you are counting in the sample.
Number_sampled The size of the sample.
Population_successes The number of successes in the population.
Population The size of the population.
Cumulative A logical value that determines the type of distribution to return. FALSE returns the probability mass function TRUE returns the cumulative distribution function. Defaults to FALSE if omitted.


When you need to delve into the probabilities associated with sampling without replacement, Excel's HYPGEOM.DIST function is your go-to tool. It facilitates the assessment of how likely it is to obtain a specific number of successes in a sample drawn from a finite population, playing a crucial role in statistical analyses where outcomes are interdependent and influenced by sampling choices without replacement. In essence, it shines as a reliable resource for determining the likelihood of various success scenarios in such contexts. To employ HYPGEOM.DIST effectively, you specify key parameters related to the sample and population. These include the number of successes you are interested in within the sample, the sample's size, the total number of successes in the entire population, and the population size. Additionally, you have the flexibility to indicate whether you want the probability mass function or the cumulative distribution function through the optional [Cumulative] argument. This versatility ensures that you receive the desired output format based on your statistical analysis requirements. HYPGEOM.DIST empowers users to gain insights into the likelihoods of different success outcomes, enabling informed decision-making and comprehensive statistical evaluations in a wide range of scenarios. By offering precise probability calculations in sampling situations without replacement, this function proves invaluable for professionals engaged in statistical analysis, research, and decision-making processes.


Imagine you are sampling 5 cards from a deck of 52 cards (without replacement) and want to find the probability of getting exactly 2 black cards. The HYPGEOM.DIST formula to use would be: =HYPGEOM.DIST(2, 5, 26, 52, FALSE)
This will give you the probability of obtaining 2 black cards out of the 5 drawn from the deck.

Suppose you are selecting 3 marbles from a bag of 10 red and 5 blue marbles (without replacement) and wish to know the probability of selecting at most 1 red marble. The HYPGEOM.DIST function to calculate this would be: =HYPGEOM.DIST(1, 3, 10, 15, TRUE)
This will provide you with the cumulative probability of selecting at most 1 red marble out of the 3 marbles picked from the bag.


How does HYPGEOM.DIST differ from other probability distribution functions in Excel?

HYPGEOM.DIST specifically deals with the hypergeometric distribution, which is used for sampling without replacement scenarios. Unlike functions such as BINOM.DIST or POISSON.DIST that handle binomial or Poisson distributions respectively, HYPGEOM.DIST considers the interdependence of outcomes in finite populations.

Can the HYPGEOM.DIST function handle scenarios with a large population size?

Yes, the HYPGEOM.DIST function can accommodate scenarios with large population sizes. As long as the sample size, number of successes, and other parameters are correctly provided, it can perform accurate probability calculations for a wide range of sampling scenarios.

What does the [Cumulative] argument in the HYPGEOM.DIST function signify?

The [Cumulative] argument in the HYPGEOM.DIST function determines the type of distribution to return. If set to FALSE, it returns the probability mass function, while setting it to TRUE provides the cumulative distribution function. This allows users to choose the form of probability output they require based on their analytical needs.

Related functions