What Is HYPGEOM.DIST Function in Google Sheets?
The HYPGEOM.DIST function in Google Sheets finds the probability of obtaining a certain number of successes in a sample drawn from a finite population without replacement, that follows a hypergeometric distribution. For the uninitiated, the hypergeometric distribution is a kind of probability distribution that shows the likelihood of a particular number of successes in a sample drawn without replacement from a finite population. Here, once an item is drawn, it cannot be replaced. It is unlike the binomial distribution which assumes independent trials. The hypergeometric distribution is for dependent trials.
For example, we have a box with 20 balls (population size), 7 red (successes). If you randomly select four balls (sample size), the HYPGEOM.DIST function can find the probability of getting exactly two red balls in your sample. The syntax of the function is as follows. Hence, we apply:
=HYPGEOM.DIST(2, 4, 7, 20, FALSE).
When we randomly draw 4 balls from the box, the probability of exactly 2 red balls is 33%.
Key Takeaways
- The HYPGEOM.DIST function is used to find the probability of a certain number of successes in a series of draws without replacement in a hypergeometric distribution.
- The syntax of the function is
=HYPGEOM.DIST(num_successes, num_draws, successes_in_pop, pop_size).
- It returns the probability mass function if cumulative is given as FALSE (default) or the cumulative distribution function if cumulative is TRUE.
- It is used to manage inventory, in analyzing quality control data, and for card and dice-related games to predict outcomes.
Syntax
Let us study the syntax in detail. The HYPGEOM.DIST formula in Google Sheets is:
=HYPGEOM.DIST(sample_s, sample_size, population_s, population_size, cumulative).
Here, the arguments mean:
- sample_s: The number of successes you want to find in your sample.
- sample_size: The total number of items in the sample.
- population_s: The total number of successes in the whole population.
- population_size: The total size of the entire population.
- cumulative: TRUE or FALSE. If TRUE, it returns the cumulative probability. This is the probability of getting at least a certain number of successes. If FALSE, it returns the probability mass function, which is the probability of getting exactly a particular number of successes.
How To Use HYPGEOM.DIST Function in Google Sheets?
Wondering where are the possibilities of using HYPGEOM.DIST? It can be used to draw colored balls and check the probability of a certain number of successes, in quality control to find defective vs. non-defective items, and in card games for drawing specific cards from a deck. However, how do you use it in Google Sheets?
We can use the HYPGEOM.DIST Google Sheets function in two ways.
- Entering the HYPGEOM.DIST function manually.
- Entering through the Google Menu Bar
Entering HYPGEOM.DIST Function Manually
TO understand how to enter the function manually, let us take a simple example. We wish to calculate the probability of drawing 4 green marbles when we draw 6 times from an urn containing 20 marbles with 8 green ones. First, we enter all these details in a Google Sheet as shown below.
Step 1: To find the probability, enter the details in the Google Sheet.
Step 2: Then, start to enter the formula as shown below in cell B6.
=HYPGEOM.DIST(
Step 3: Enter the arguments in the order shown. Each argument is separated by a comma. We can directly enter the values as arguments separated by commas or enter their cell references. Here, we enter their cell references. Press Enter. You get the value of the probability in cell B6.
Hence, the probability that the value x is less than or equal to 7 is 15.5%.
Explanation: The value returned, which is a decimal between 0 and 1, represents the probability of getting exactly 4 green marbles in the 6-marble sample.
Here, Google Sheets returns 0.1192, which means there’s about an 11.92% chance of drawing exactly 4 green marbles when we pick 6 at random.
Entering Through the Google Menu Bar
- Place the cursor where you want the formula to be entered.
- Then, go to Insert -> Function -> Statistical -> HYPGEOM.DIST.
- Select it, type in the required arguments, and close the braces.
- Press Enter to get the result.
Examples
Apart from drawing balls, where would we use this function? There are plenty of common scenarios where HYPGEOM.DIST can be used. Let’s explore some practical examples:
Example #1 – Determine the probability of finding a certain number of defective items in your sample
In this example, we have some spare parts required by a factory. There is a bag containing 150 spare parts. According to testing data, there are around 25 defective items among them. We randomly check 15 spare parts without replacement. What’s the probability of drawing exactly 5 defective parts?
Step 1: Let us enter all the details in a sheet as shown below.
Step 2: Let us enter the following formula in a spreadsheet cell:
=HYPGEOM.DIST(A1, A2, A3, A4, FALSE)
Explanation:
- A1 means we wish to find the probability of drawing exactly five defective parts (successes).
- A2: We will be drawing a sample of 15 spare parts.
- A3: There are 25 defective parts in the population.
- A4: The total number of spare parts in the population.
- FALSE: We wish to find the probability mass function
The function would return the probability of drawing exactly 5 defective parts in our drawing of 15.
Step 3: Press Enter.
This will return the probability of getting exactly 4 defective parts in a collection of 8 parts.
Example #2 – Calculate the likelihood of drawing a specific combination of cards from the deck
Now that we have seen an example above, which helps us plan our inventory better and improve quality, let us go to a standard example – the likelihood of drawing a specific combination of cards from a deck. We have a deck of 52 cards. We must draw 4 cards from it.
There are 4 Kings in the deck. Let us find the probability of getting exactly 2 kings when you draw 4 cards.
Step 1: Enter the details as shown below.
Step 2: We will implement the HYPGEOM.DIST, which has the following syntax.
=HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative)
We enter the following formula:
=HYPGEOM.DIST(A1, A2, A3, A4, FALSE)
- A1 means we wish to find the probability of drawing exactly two kings (successes).
- A2: The sample of 8 spare parts.
- A3: There are 4 kings in the population.
- A4: The total number of cards in the deck
- FALSE: We wish to find the probability mass function.
Step 3: Press Enter. This will return the probability of getting exactly 2 kings in a hand of 4 cards.
In case, you wish to find the probability of getting 2 or fewer kings, we can use the following:
=HYPGEOM.DIST(2, 4, 4, 52, TRUE)
Example #3 – Know the probability of picking at least 10 high-demand items in a random sample of 30
In this example, we have a population size of 100 items. There are 20 high-demand items, which are the number of successes. The sample size is 30; we draw 30 items from the population.
You want the probability of getting at least 10 high-demand items when we draw 30.
Step 1: HYPGEOM.DIST doesn’t directly support the process of picking at least 10 items. In this case, we can find the cumulative probability of getting fewer than 10. (i.e., cumulative from 10 to 30). We compute it by subtracting the value from 1.
=1 – HYPGEOM.DIST(9, 30, 20, 100, TRUE)
Press Enter. This gives the probability of getting 10 or more high-demand items in a sample of 30.
Important Things to Note
- The HYPGEOM.DIST function assumes items are drawn without replacement.
- It is mainly used for quality control, inventory, card draws, etc.
- Ensure that the values are written correctly. For instance, the sample size should not be greater than the population. Check if the right Boolean value is entered according to the probability to be found.
- Be sure all your numbers make sense (e.g., sample size can’t be bigger than population).
- All values must be positive integers.
Frequently Asked Questions (FAQs)
Let us look at some practical scenarios where we use HYPGEOM.DIST.
1. Quality Control: HYPGEOM.DIST, you can determine the probability of finding a certain number of defective items in a sample. It is useful when you wish to perform quality checks and find the quality of the batch.
2. Card Games: We use the function to find the odds of drawing cards/items without replacement. It is useful for modeling fairness or outcomes in games.
3. Market Research: It is used for analyzing feedback samples to understand the positives and negatives of a service or product.
When we give this argument as TRUE, it returns the cumulative probability. When we set it as FALSE, it returns the exact probability.
In other words, TRUE calculates the probability of obtaining up to and including the specified number of successes. When it is FALSE, it calculates the probability of drawing the exact specified number of successes.
The HYPGEOM.DIST function models probabilities in scenarios where one makes selections without replacement in a finite population. Here, each draw affects the next. Here, we have a small population, and it reflects dependent events. However, with BINOM.DIST, we assume independent trials with replacement or there is a constant probability, like when throwing a die. BINOM.DIST works for independent events. Both functions calculate the probability of a particular number of successes in a sample.
Download Template
This article must help understand HYPGEOM.DIST Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is HYPGEOM.DIST Function in Google Sheets. We learn its syntax & how to use it in Google Sheets with step-wise examples. You can learn more from the following articles. –
Leave a Reply