Author: Oscar Cronquist Article last updated on February 28, 2023

How to use the HYPGEOM DIST function

The HYPGEOM.DIST calculates the hypergeometric distribution.

A hypergeometric distribution is a probability distribution that describes the number of successes in a fixed-size sample drawn from a finite population without replacement. It is similar to the binomial distribution (BINOM.DIST function) which describes the number of successes in a fixed-size sample drawn from a finite population with replacement.

In the hypergeometric distribution, each draw is dependent on the previous draw, as the sample size decreases with each draw. The geometric distribution becomes increasingly similar to the binomial distribution if the population size grows larger.

1. HYPGEOM.DIST Function Syntax

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

Back to top

2. HYPGEOM.DIST Function Arguments

sample_s Required. A number representing how many successes in the sample.
number_sample Required. The sample size.
population_s Required. The total number of successes in the entire population.
number_pop Required. The population size.
cumulative Required. A logical value that determines the form of the function.
TRUE - HYPGEOM.DIST returns the cumulative distribution function.
FALSE - HYPGEOM.DIST returns the probability mass function.

Back to top

3. HYPGEOM.DIST Function example

How to use the HYPGEOM DIST function5

This example uses the HYPGEOM.DIST function to calculate the probability of getting a spade from a deck of cards (standard 52 cards). The distribution chart, and the table next to it, show the probability of getting 1 spade, 2 spades, 3 spades, ...  and up to 13 spades based on 13 cards out of 52 cards.

sample_s - 3
number_sample - 13
population_s - 13
numbeer_pop - 52
cumulative - FALSE

Formula in cell C18:

=HYPGEOM.DIST($B18,$C$3,$C$4,$C$5,C$13)

For example, you get 13 cards out of 52 cards. What is the chance that you get exactly 3 spades and the remaining cards are anything else (clubs, diamonds, and hearts)?

The table in B15:D27 shows that if the sample_s is 3 HYPGEOM.DIST returns approx 0.286 in cell C17, the probability is 28.6%.

What is the probability of getting up to 3 spades?

How to use the HYPGEOM DIST function2

Valid outcomes are 0, 1, 2, and 3 but not more than 3 spades. The cumulative values are calculated in column TRUE, sample_s is 3 and the corresponding cumulative value is approx 0.585 or 58.5%

The cumulative value is calculated like this: 0.013 + 0.08 + 0.206 + 0.286 equals approx 0.585

How to use the HYPGEOM DIST function3

Back to top