Excel HYPGEOM.DIST Function

The HYPGEOM.DIST function returns the value of the hypergeometric distribution, which is the probability for a specified number of successes from a population sample. The hypergeometric distribution is used to calculate probabilities of a given number of successes in a sample without replacement from a finite population.

Syntax

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

Arguments

Return value

The HYPGEOM.DIST function returns a numeric value.

Function notes

1. The HYPGEOM.DIST function was newly introduced In Excel 2010, so it is unavailable in earlier versions.
2. The #VALUE! error value occurs if any of the supplied arguments is non-numeric.
3. The #NUM! error value occurs if any of the below situation occurs:
   • The supplied sample_s is < 0;
   • The supplied number_sample < 0 or > number_pop;
   • The supplied population_s < 0 or > number_pop;
   • The supplied number_pop ≤ 0.
4. Zero (0) will be returned when supplied sample_s is < (number_sample - number_population + population_s) value.
5. The equation for the hypergeometric distribution is:
   
   Where x means sample_s, n means number_sample, M means population_s, and N means number_pop.

Examples

Example One: Cumulative Distribution Function

In this case, you have a bag, inside which are 12 red balls and 8 blue balls. Now if you reach in the bag and pick 6 balls from it, what is the possibility of getting at most 3 blue balls?

Please copy the formula below into cell H5, then press the Enter key to get the result.

=HYPGEOM.DIST(B5,C5,D5,E5,F5)

Note: We can also directly input values in the formula. The formula in cell H5 can be changed to:

=HYPGEOM.DIST(3,6,8,20,TRUE)

Example Two: Probability Density Function

Like the situation above, you also have a bag, inside which are 12 red balls and 8 blue balls. Now if you reach in the bag and pick 6 balls from it, what is the possibility of getting exactly 3 blue balls?

Please copy the formula below into cell H6, then press the Enter key to get the result.

=HYPGEOM.DIST(B6,C6,D6,E6,F6)

Note: We can also directly input values in the formula. The formula in cell H6 can be changed to:

=HYPGEOM.DIST(3,6,8,20,FALSE)

Relative Functions:

• Excel EVEN Function
  The EVEN function rounds numbers away from zero to the nearest even integer.

• Excel EXP Function
  The EXP function returns the result of the constant e raised to the nth power.