The Excel HYPGEOM.DIST Function

Hypergeometric Distribution

The hypergeometric distribution gives the probability of a specific number of successes from a given number of draws, from a finite population, without replacement.

The two forms of the hypergeometric distribution, that are calculated by the Excel Hypgeom.Dist function are:

Probability Density Function	-	Calculates the probability of exactly n successes from a given number of trials
Cumulative Function	-	Calculates the probability of at most n successes from a given number of trials

Function Description

The Excel Hypgeom.Dist function returns the value of the hypergeometric distribution for a specified number of successes from a population sample.

The function can calculate the cumulative distribution or the probability density function.

Note that the Hypgeom.Dist function is new in Excel 2010, and so is not available in earlier versions of Excel. However, the probability density hypergeometric distribution can be calculated by the Hypgeomdist function, which is available in earlier versions of Excel.

The syntax of the Excel Hypgeom.Dist function is:

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

Where the function arguments are:

sample_s

The number of successes in the sample.

number_sample

The size of the sample.

population_s

The number of successes in the population.

number_pop

The size of the population.

cumulative

A logical argument that specifies the type of distribution to be calculated. This can have the value TRUE or FALSE, meaning:

TRUE	-	Calculate the cumulative distribution function
FALSE	-	Calculate the probability density function

The first four arguments to the function should all be integers. If these values are supplied as decimals, they are truncated to integers by Excel.

Hypgeom.Dist Function Examples

Example 1: Probability Density Function

Imagine you have a bag, containing 3 red balls and 6 blue balls. If you remove 3 balls from the bag, the probability that 0, 1, 2 or 3 of these balls are red can be calculated by the hypergeometric distribution.

These probabilities are calculated by the Excel Hypgeom.Dist function, in cells A2-A5 of the following spreadsheet:

Formulas:

	A	B
1	Select 3 balls from a bag containing 3 red balls & 6 blue balls:
2	=HYPGEOM.DIST( 0, 3, 3, 9, FALSE )	- probability of exactly 0 red balls
3	=HYPGEOM.DIST( 1, 3, 3, 9, FALSE )	- probability of exactly 1 red ball
4	=HYPGEOM.DIST( 2, 3, 3, 9, FALSE )	- probability of exactly 2 red balls
5	=HYPGEOM.DIST( 3, 3, 3, 9, FALSE )	- probability of exactly 3 red balls

Results:

	A	B
1	Select 3 balls from a bag containing 3 red balls & 6 blue balls:
2	0.238095238	- probability of exactly 0 red balls
3	0.535714286	- probability of exactly 1 red ball
4	0.214285714	- probability of exactly 2 red balls
5	0.011904762	- probability of exactly 3 red balls

Note that the above example calculates the probability density function. This gives the probability of exactly sample_s successes occurring.

Example 2: Cumulative Distribution Function

Imagine that you have the same bag as in Example 1, which again, contains 3 red balls and 6 blue balls.

The Excel Hypgeom.Dist function, can also be used to the calculate the probability of at most 0, 1, 2 or 3 red balls being selected from a sample of 3 balls, taken from the bag.

This is shown in the following spreadsheet:

Formulas:

	A	B
1	Select 3 balls from a bag containing 3 red balls & 6 blue balls:
2	=HYPGEOM.DIST( 0, 3, 3, 9, TRUE )	- probability of at most 0 red balls
3	=HYPGEOM.DIST( 1, 3, 3, 9, TRUE )	- probability of at most 1 red ball
4	=HYPGEOM.DIST( 2, 3, 3, 9, TRUE )	- probability of at most 2 red balls
5	=HYPGEOM.DIST( 3, 3, 3, 9, TRUE )	- probability of at most 3 red balls

Results:

	A	B
1	Select 3 balls from a bag containing 3 red balls & 6 blue balls:
2	0.238095238	- probability of at most 0 red balls
3	0.773809524	- probability of at most 1 red ball
4	0.988095238	- probability of at most 2 red balls
5	1	- probability of at most 3 red balls

For further examples of the Excel Hypgeom.Dist function, see the Microsoft Office website.

Hypgeom.Dist Function Errors

If you get an error from the Excel Hypgeom.Dist function this is likely to be one of the following:

Common Errors

#NUM!

Occurs if either:

The supplied sample_s is < 0 or > number_sample;
The supplied sample_s > population_s;
The supplied sample_s > (number_sample - number_pop + population_s);
The supplied number_sample ≤ 0 or > number_pop;
The supplied population_s ≤ 0 or > number_pop;
The supplied number_pop ≤ 0.

#VALUE!

Occurs if any of the supplied arguments is non-numeric.

Return to the Excel Statistical Functions Page
Return to the List of All Built-In Excel Functions