Syntax. HYPGEOM.DIST(sample_s,number_sample,population_s,number_ population,cumulative)
Definition. This function returns the probabilities of a hypergeometric distributed random variable. HYPGEOM.DIST() calculates the probability of a certain number of observations in a sample.
The following information is required:
Number of successes in the sample
Size of the sample
Number of possible successes in the population
Size of the population
Logical value for the function
Arguments
sample_s (required). The number of successes in the sample
number_sample (required). The size of the sample
population_s (required). The number of successes in the population
number_population (required). The size of the population
cumulative (required). The logical value that represents the type of the function
Note
All arguments are truncated to integers by removing the decimal places. If one of the arguments isn’t a numeric expression, the HYPGEOM.DIST() function returns the #VALUE! error.
If sample_s is less than 0 or greater than the lesser value of number_sample or population_s, HYPGEOM.DIST() returns the #NUM! error. If sample_s is less than the larger value of 0 or (number_sample – number_population + population_s), HYPGEOM.DIST() returns the #NUM! error.
If number_sample is smaller than 0 or greater than number_population, HYPGEOM. DIST() returns the #NUM! error.
If population_s is less than 0 or greater than number_population, HYPGEOM.DIST() returns the #NUM! error.
If number_population is less than 0, HYPGEOM.DIST() returns the #NUM! error.
Use HYPGEOM.DIST() to take samples from a finite population without replacing these samples.
Background. The hypergeometric distribution answers the question, “What is the probability of finding x characteristics in a sample?” Because a random sample is taken from an entire population, you cannot use binomial distribution. The equation for the hypergeometric distribution is:
where
x = sample_s
n = number_sample
M = population_s
N = number_population
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.
Example. A simple example for explaining the HYPGEOM.DIST() function is a game of luck. The HYPGEOM.DIST() function provides a simple method to calculate the chances of winning the lottery.
The following list defines the arguments for the lottery example:
The sample_s argument is the number of successes in the sample. For example, a drawing can consist of up to six winning numbers. Therefore, sample_s = 6.
The number_sample argument is the size of the sample. Therefore, number_sample = 6.
The population_s argument is the number of possible successes in the population — the number of winning balls. Therefore, population_s = 6.
The number_population argument is the size of the population: 49 balls. Therefore, number_population = 49.
The cumulative argument is FALSE and indicates the logical value for the function.
What is the probability of having six winning numbers? Figure 12-77 shows the answer.
The probability of winning big is miniscule. Of course, you can use the HYPGEOM.DIST() function to calculate the probability for five, four, or three numbers if you settle for smaller winnings. Figure 12-78 shows the result.
