How to use the HYPGEOM.DIST function
What is the HYPGEOM.DIST function?
The HYPGEOM.DIST function calculates the hypergeometric distribution.
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.
Table of Contents
1. HYPGEOM.DIST Function Syntax
HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative)
2. 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. |
3. HYPGEOM.DIST Function example
This example uses the HYPGEOM.DIST function to calculate the probability of getting a spade from a deck of cards (standard 52 cards). The distribution chart, and the table next to it, show the probability of getting 1 spade, 2 spades, 3 spades, ... and up to 13 spades based on 13 cards out of 52 cards.
sample_s - 3
number_sample - 13
population_s - 13
numbeer_pop - 52
cumulative - FALSE
Formula in cell C18:
For example, you get 13 cards out of 52 cards. What is the chance that you get exactly 3 spades and the remaining cards are anything else (clubs, diamonds, and hearts)?
The table in B15:D27 shows that if the sample_s is 3 HYPGEOM.DIST returns approx 0.286 in cell C17, the probability is 28.6%.
What is the probability of getting up to 3 spades?
Valid outcomes are 0, 1, 2, and 3 but not more than 3 spades. The cumulative values are calculated in column TRUE, sample_s is 3 and the corresponding cumulative value is approx 0.585 or 58.5%
The cumulative value is calculated like this: 0.013 + 0.08 + 0.206 + 0.286 equals approx 0.585
Useful resources
HYPGEOM.DIST function - Microsoft support
Functions in 'Statistical' category
The HYPGEOM.DIST function function is one of many 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