Mashhura Jahan
If you have a hypergeometric distribution problem and you are looking for a way to calculate the probability, then this article will be beneficial to you. The focus of this article is to explain how to calculate the probability of hypergeometric distribution in Excel.
Download Practice Workbook
Formula for Probability of Hypergeometric Distribution
Hypergeometric Distribution generally refers to a discrete probability distribution. It mainly describes the probability of success in a draw. The formula for the probability of hypergeometric distribution is,
Here,
K = Number of Successes in Population
N = Population Size
k = Number of Successes in Sample (Observed Successes)
n = Sample Size (Number of Draws)
Now, KCk is the combination of k things drawn from K things. The formula for combination is,
The same formula can be used to find other combinations.
2 Ways to Calculate Probability of Hypergeometric Distribution in Excel
Suppose, you have 10 flags in a box. 5 of the flags are black and the other 5 are white. Now, you want to calculate the probability of drawing 3 black flags if you randomly draw 4 flags from the box. So, the Sample Size is 4, the Number of Successes in Sample is 3, the Number of Successes in Population is 5, and the Population Size is 10. You can see that I have entered these data in the following dataset. I will use this dataset to show you how to calculate the probability of hypergeometric distribution in Excel.
1. Apply COMBIN Function to Calculate Probability of Hypergeometric Distribution in Excel
In this first method, I will use the COMBIN function to calculate the probability of hypergeometric distribution in Excel. Here, I will use the generic formula and in the formula, I will use the COMBIN function to calculate the combinations. Let’s see the steps of this calculation.
Steps:
- Firstly, select the cell where you want to calculate the Probability. Here, I selected cell C9.
- Secondly, in cell C9 write the following formula.
=COMBIN(C6,C4)*COMBIN(C7-C6,C5-C4)/COMBIN(C7,C5)
- Finally, press Enter to get the Probability.
🔎 How Does the Formula Work?
- COMBIN(C6,C4): Here, in the COMBIN function, I selected cell C6 as the number and cell C4 as the number_chosen. The formula returns the number of combinations for these specified number of items.
- COMBIN(C7-C6,C5-C4): Now, the COMBIN function returns the number of combinations for C7-C6 and C5-C4.
- COMBIN(C7,C5): Here, the COMBIN function returns the number of combinations for the values in cells C7 and C5.
- COMBIN(C6,C4)*COMBIN(C7-C6,C5-C4)/COMBIN(C7,C5): Now, the first 2 numbers of combinations are multiplied and then the result is divided by the last number of combinations.
Read More: How to Calculate Probability Distribution in Excel (with Quick Steps)
Similar Readings
- How to Get Simulation Probability in Excel (with Easy Steps)
- How to Create Joint Probability Table in Excel (with Easy Steps)
- How to Use Continuous Probability Distribution in Excel
- How to Calculate Probability of Exceedance in Excel
- How to Make a Probability Tree Diagram in Excel (3 Easy Methods)
2. Get Hypergeometric Probability Distribution Using Excel HYPGEOM.DIST Function
Here, I will use the HYPGEOM.DIST function to calculate the probability of hypergeometric distribution in Excel. The HYPGEOM.DIST function returns the Probability of a given Number of Successes in Sample, given the Sample Size, Number of Successes in Population, and Population Size. Here, I will explain 2 different calculations using the same function. One for probability density and another for cumulative distribution.
2.1. Use HYPGEOM.DIST Function to Get Probability Density
In this first method, I will calculate the probability of hypergeometric distribution in Excel using the HYPGEOM.DIST function. I will calculate the probability of drawing 3 black flags if you randomly draw 4 flags from a box containing 10 flags among which 5 are black.
Let’s see the steps.
Steps:
- In the beginning, select the cell where you want the Probability.
- Then, write the following code in the selected cell.
=HYPGEOM.DIST(C4,C5,C6,C7,FALSE)
- Lastly, press Enter and you will get the Probability.
2.2. Employ HYPGEOM.DIST Function to Calculate Cumulative Distribution
Now, I will calculate the cumulative distribution using the HYPGEOM.DIST function in Excel. Suppose you want to calculate the probability of drawing at most 3 black flags if you randomly draw 4 flags from a box containing 10 flags among which 5 are black. Here, you will get the cumulative probability of drawing 1 black flag, 2 black flags, and 3 black flags. Let’s see the calculation.
Steps:
- Firstly, select the cell where you want the Probability. Here, I selected cell C9.
- Next, in cell C9 write the following formula.
=HYPGEOM.DIST(C4,C5,C6,C7,TRUE)
- After that, press Enter to get the result.
Read More: How to Calculate Probability Density Function in Excel
Practice Section
Here, I have provided a practice sheet for you to calculate the probability of hypergeometric distribution in Excel.
Conclusion
Finally, you have come to the end of my article. Here, I tried to explain 2 different methods of calculating the probability of hypergeometric distribution in Excel. I hope this article was clear to you. You can visit ExcelDemy for more articles like this. Lastly, if you have any questions feel free to let me know in the comment section below.
Related Articles
- How to Calculate Variance of Probability Distribution in Excel
- How to Model Uniform Probability Distribution in Excel (3 Ways)
- Poisson Probability Distribution in Excel (4 Practical Examples)
- How to Calculate Conditional Probability in Excel (2 Easy Ways)
- How to Create Option Probability Calculator in Excel
- Calculating Probability in Excel with Mean and Standard Deviation
