How to use the HYPGEOM.DIST function
What is the HYPGEOM.DIST function?
The HYPGEOM.DIST function calculates the hypergeometric distribution.
Table of Contents
1. Introduction
What is a hypergeometric distribution?
A hypergeometric distribution is a probability distribution that describes the number of successes in a fixed-size sample drawn from a finite population without replacement. It is similar to the binomial distribution (BINOM.DIST function) which describes the number of successes in a fixed-size sample drawn from a finite population with replacement.
In the hypergeometric distribution, each draw is dependent on the previous draw, as the sample size decreases with each draw. The geometric distribution becomes increasingly similar to the binomial distribution if the population size grows larger.
What is a geometric distribution?
The geometric distribution is a discrete probability distribution that models the number of independent trials needed to achieve the first success. It is useful for modeling failure rates in manufacturing systems, sports, and biology.
What is a binomial distribution?
The binomial distribution is a discrete probability distribution modeling the total number of successes in a fixed number of independent identically distributed Bernoulli trials. It is useful for estimating proportions like election outcomes or manufacturing defect rates.
2. HYPGEOM.DIST Function Syntax
HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative)
3. HYPGEOM.DIST Function Arguments
| sample_s | Required. A number representing how many successes in the sample. |
| number_sample | Required. The sample size. |
| population_s | Required. The total number of successes in the entire population. |
| number_pop | Required. The population size. |
| cumulative | Required. A logical value that determines the form of the function. TRUE - HYPGEOM.DIST returns the cumulative distribution function. FALSE - HYPGEOM.DIST returns the probability mass function. |
What is a probability mass function?
A probability mass function (PMF) is a function that gives the probability that a discrete random variable takes on each of its possible values.
What is a discrete random variable?
A discrete random variable is one that can take on only certain distinct values, such as counts or whole numbers.
What is the cumulative distribution function?
In contrast to the PMF, the cumulative distribution function (CDF) gives the probability that a random variable takes on a value less than or equal to a certain value. The CDF is used for both discrete and continuous random variables.
4. HYPGEOM.DIST Function Example 1
What is the probability of getting exactly two spades from a deck of cards (standard 52 cards) in a 7-card game?
This example uses the HYPGEOM.DIST function to calculate the probability of getting two spades in a 7-card game. Here are the arguments:
| Text | Arg | Descr |
| sample_s | 2 | 2 spades |
| number_sample | 7 | 7-card game |
| population_s | 13 | 13 spades in a standard deck |
| number_pop | 52 | 52 cards in a standard deck |
| cumulative | FALSE | calculate probability of getting exactly two spades |
Cell range C14:C18 in the image above contains the arguments.
Formula in cell C20:
The calculation returns a probability of 33.57% of getting exactly 2 spades in a 7-card game based on a standard deck of cards containing 52 cards.
The image above shows a probability distribution, the first column in the chart represents the probability of getting one spade in a 7-card game. The second column shows the probability of getting two spades in a 7-card game. The third chart column shows the probability of getting three spades etc.
The chart columns represent discrete variables, for example, there can't be 1.5 spades in a 5-card game.
5. HYPGEOM.DIST Function Example 2
In a standard deck of 52 cards, consider a game where you draw 5 cards. We want to find the probability of drawing 0, 1, or 2 diamond cards in that 5-card hand. In other words, we are interested in the probability of getting at most 2 diamonds in the 5-card draw. The possible favorable outcomes are:
- Drawing 0 diamonds
- Drawing 1 diamond
- Drawing 2 diamonds
What is the probability of obtaining any one of these three outcomes when drawing 5 cards from a standard 52-card deck?
Here are the arguments:
| Text | Arg | Descr |
| sample_s | 2 | 2 spades |
| number_sample | 5 | 5-card game |
| population_s | 13 | 13 spades in a standard deck |
| number_pop | 52 | 52 cards in a standard deck |
| cumulative | TRUE | calculate probability of getting at most two spades |
Cell range C14:C18 in the image above contains the arguments.
Formula in cell C20:
The calculation returns a probability of 90.72% of getting at most 2 diamonds in a 5-card game based on a standard deck of cards containing 52 cards.
6. HYPGEOM.DIST Function Example 3
What is the possibility of getting 2 or more hearts in a 5-card game of a standard 52-card deck?
Here are the arguments:
| Text | Arg | Descr |
| sample_s | 1 | 2 hearts |
| number_sample | 5 | 5-card game |
| population_s | 13 | 13 spades in a standard deck |
| number_pop | 52 | 52 cards in a standard deck |
| cumulative | TRUE | calculate probability of getting at most 1 heart |
Cell range C14:C18 in the image above contains the arguments. Formula in cell C20:
HYPGEOM.DIST(C14,C15,C16,C17,C18) returns the cumulative probability of approx. 63.30% of getting at most 1 diamond in a 5-card game based on a standard deck of cards containing 52 cards. However, we want to know the probability of getting two or more diamonds. We need to calculate the complement by subtracting 1 with the probability. This gives us 36.3% (1-0.6330 = 0.363)
7. HYPGEOM.DIST Function Example 4
A survey is conducted on 200 people, and 80 of them have a positive opinion about a particular product. If 15 people are randomly selected for a focus group, what is the probability of finding exactly 5 people with a positive opinion?
Here are the arguments:
| Text | Arg | Descr |
| sample_s | 5 | finding 5 positive opinion |
| number_sample | 15 | 15 people randomly selected |
| population_s | 80 | 80 people with a positive opinion |
| number_pop | 200 | 200 people total in survey |
| cumulative | FALSE | find EXACTLY 5 people |
Cell range C14:C18 in the image above contains the arguments. Formula in cell C20:
HYPGEOM.DIST(C14,C15,C16,C17,C18) returns the probability of approx. 19.07% of getting 5 positive people in 15 randomly selected in a group of 200 people and 80 have a positive opinion.
Useful resources
HYPGEOM.DIST function - Microsoft support
Functions in 'Statistical' category
The HYPGEOM.DIST function function is one of 73 functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form