HYPGEOM.DIST Function

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.

Syntax: 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:

Examples of use of the Excel Hypgeom.Dist Function

 Results:

Excel Hypgeom.Dist Function 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:

Examples of use of the Excel Hypgeom.Dist Function

 Results:

Excel Hypgeom.Dist Function Results

 

Hypgeom.Dist Function Errors

If you get an error from the Excel Hypgeom.Dist function this is likely to be one of the following :

#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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment