The HYPGEOM.DIST function returns the hypergeometric distribution – the probability of a given number of successes from a given population size.

  • This function was introduced in Excel 2010 and so is not available in earlier versions.
  • HYPGEOM.DIST replaces the HYPGEOMDIST function included in earlier versions of Excel.

Syntax

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

Arguments

Argument Description
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
cumulative A logical value that determines the form of the function

  TRUE returns the cumulative distribution function
  FALSE returns the probability density function

Examples

  A B C D
1 Data Description    
2 8 Number of successes in the sample    
3 20 Sample size    
4 40 Number of successes in the population    
5 100 Population size    
6        
7 Formula Result Notes
8 =HYPGEOM.DIST(A2,A3,A4,A5,TRUE) 0.604187 Cumulative hypergeometric distribution function, for sample and population in cells A2:A5
9 =HYPGEOM.DIST(A2,A3,A4,A5,FALSE) 0.200785 Probability hypergeometric distribution function, for sample and in cells A2:A5

Usage note: Use HYPGEOM.DIST for problems with a finite population, where each observation is either a success or a failure, and where each subset of a given size is chosen with equal likelihood.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if any of the supplied arguments are not recognized as numeric values
#NUM! Occurs if either:

  the supplied sample_s is < 0 or > number_sample
  the supplied number_sample ≤ 0 or > number_population
  the supplied population_s ≤ 0 or > number_population
  the supplied number_population ≤ 0

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 equation for the hypergeometric distribution is:    

    \[    P(X = x) = h(x;n,M,N) =  \frac {               \left( \begin{matrix} M \\ x \end{matrix} \right)               \left( \begin{matrix} N - M \\ n - x \end{matrix} \right)            }           { \left( \begin{matrix} N \\ n \end{matrix} \right)           }    \]

where:    

    \[\begin{aligned}x  &= \text{sample\_s}\\n  &= \text{number\_sample}\\M &= \text{population\_s}\\N &= \text{number\_pop}\end{aligned}    \]

See Wikipedia for more information on hypergeometric distribution.