HYPGEOM.DIST Function - Hypergeometric Distribution in Excel

This article introduces the HYPGEOM.DIST function - one of the statistical functions highly favored in Excel.

Description: Returns the hypergeometric distribution, used for finite population sampling problems where observations can be successes or failures.

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

In which:

- sample_s: Number of successful events in the sample, a mandatory parameter.

- number_sample: Size of the sample, a mandatory parameter.

- population_s: Number of successful events in the population, a mandatory parameter.

- number_pop: Size of the population, a mandatory parameter.

- cumulative: Logical value determining the form of the function, an optional parameter with the following values:

+ cumulative = True -> returns the cumulative distribution function.

+ cumulative = False -> returns the probability mass function.

Note:

- Any argument of the function that is not an integer -> will be rounded to the nearest integer.

- If any argument value is not numeric -> the function returns the error value #VALUE!

- If sample_s < 0 or sample_s is greater than the smaller of the two numbers: number_sample or population_s -> the function returns the error value #NUM!

- If sample_s is less than the larger of the two numbers: 0 and (number_sample - number_population + population_s) -> the function returns the error value #NUM!

- If number_sample ≤ 0 or number_sample > number_population -> the function returns the error value #NUM!

- If population_s ≤ 0 or population_s > number_population -> the function returns the error value #NUM!

- If number_pop ≤ 0  -> the function returns the error value #NUM!

- The hypergeometric distribution equation is:

P(X=x)=h(x;n,M,N)=(Mx)(N−Mn−x)(Nn)$P\left(X=x\right)=h\left(x;n,M,N\right)=\frac{\left(\begin{array}{c}M\\ x\end{array}\right)\left(\begin{array}{c}N-M\\ n-x\end{array}\right)}{\left(\begin{array}{c}N\\ n\end{array}\right)}$

Where:

+ x = sample_s

+ n represents the number of samples

+ M stands for the population size

+ N indicates the total population count

- The HYPGEOM.DIST function is used for sampling without replacing a finite set.

Example:

Calculate the cumulative hypergeometric distribution function and probability with the data in the table below:

- Calculate the value of the cumulative hypergeometric distribution function. In the cell where you want to calculate, enter the formula: =HYPGEOM.DIST(D6,D7,D8,D9,D10)

- Press Enter -> the value of the cumulative hypergeometric distribution function is:

- Calculate the value of the hypergeometric probability function. In the cell where you want to calculate, enter the formula: =HYPGEOM.DIST(D6,D7,D8,D9,D11)

- Press Enter -> the value of the hypergeometric probability function is:

Here is the guide and some specific examples of using the HYPGEOM.DIST function in Excel.

Wishing you success!