|
The Excel HYPGEOM.DIST Function
Home »
Excel-Built-In-Functions »
Excel-Statistical-Functions »
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 |
Basic Description
The Excel Hypgeom.Dist function returns the value of the hypergeometric distribution for a specified number
of successes from a population sample. The user can specify whether the cumulative distribution or the probability
density function should be used.
The 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 9 green 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 is given by the hypergeometric distribution.
These probabilities are calculated by the Excel Hypgeom.Dist function, in cells A2-A5 of the spreadsheet on the right.
The formulas are shown in the upper spreadsheet and the results are shown in the spreadsheet below.
Note that this example calculates the probability density function. This gives the probability
of exactly sample_s successes occurring.
|
Formulas:
|
Results:
|
|
Example 2: Cumulative Distribution Function
Imagine that you have the same bag as in Example 1 (above), which again, contains 3 red balls and 9 green 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 spreadsheet on the right. Again, the formulas are shown in the upper spreadsheet and
the results are shown in the lower spreadsheet.
|
Formulas:
|
Results:
|
|
Further examples of the Excel Hypgeom.Dist function can be found on 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 number_sample ≤ 0 or > number_population
|
| - |
the supplied population_s ≤ 0 or > number_population
|
| - |
the supplied number_population ≤ 0
|
|
|
#VALUE!
|
-
|
Occurs if any of the supplied arguments are not recognised as numeric values
|
|
