The Excel HYPGEOMDIST Function

Hypergeometric Distribution

The hypergeometric distribution is a discrete probability distribution that gives the probability of a specified number of successes from a given number of draws from a finite population, without replacement.

The Hypergeometric Distribution is given by the formula:

where,

x = number of successes in the sample;

n = number in the sample;

M = number of successes in the population;

N = population size.

HYPGEOMDIST and HYPGEOM.DIST

In Excel 2010, the Hypgeomdist function has been replaced by the Hypgeom.Dist function, which has improved accuracy.

Although it has been replaced, the Hypgeomdist function is still available in Excel 2010 (stored in the list of compatibility functions), to allow compatibility with earlier versions of Excel.

Function Description

The Excel Hypgeomdist function returns the value of the hypergeometric distribution for a given number of successes from a sample of a population.

The syntax of the function is:

HYPGEOMDIST( sample_s, number_sample,
population_s, number_pop )

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 population size.

Note that the above arguments should all be integers. If these values are supplied as decimals, they are truncated to integers by Excel.

Hypgeomdist Function Example

Formulas:

	A	B
1	Select 4 balls from a bag containing 4 red balls & 8 blue balls:
2	=HYPGEOMDIST( 1, 4, 4, 12 )	- probability of exactly 1 red ball
3	=HYPGEOMDIST( 2, 4, 4, 12 )	- probability of exactly 2 red balls
4	=HYPGEOMDIST( 3, 4, 4, 12 )	- probability of exactly 3 red balls
5	=HYPGEOMDIST( 4, 4, 4, 12 )	- probability of exactly 4 red balls

Results:

	A	B
1	Select 4 balls from a bag containing 4 red balls & 8 blue balls:
2	0.452525253	- probability of exactly 1 red ball
3	0.339393939	- probability of exactly 2 red balls
4	0.064646465	- probability of exactly 3 red balls
5	0.002020202	- probability of exactly 4 red balls

Imagine you have a bag, containing 4 red balls and 8 blue balls.

If you remove 4 balls from the bag, the probability that you have selected exactly n red balls can be calculated by the Excel Hypgeomdist function.

This is shown in cells A2-A5 of the above spreadsheet on the right.

For further information and examples of the Excel Hypgeomdist function, see the Microsoft Office website.

Hypgeomdist Function Errors

If you get an error from the Excel Hypgeomdist 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