# How to Calculate Probability of Hypergeometric Distribution in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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,

Probability = KCk * (N-K)C(n-k) / NCn

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,

KCk = K! / (k! * (K-k)!)

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)

### 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. Here, in the HYPGEOM.DIST function, I selected cell C4 as sample_s, C5 as number_sample, C6 as population_s, and C7 as number_pop. And selected FALSE for cumulative. Now, the function returns the Probability for this hypergeometric distribution.

#### 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. Here, in the HYPGEOM.DIST function, I selected cell C4 as sample_s, C5 as number_sample, C6 as population_s, and C7 as number_pop. And selected TRUE for cumulative. So, the function returns the Cumulative Probability for this hypergeometric distribution.

## 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 Mashhura Jahan

Hey! Welcome to my profile. Currently, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  