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.

**Table of Contents**hide

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

**Probability =**

_{K}C_{k }*_{(N-K)}C_{(n-k) }/_{N}C_{n}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, _{K}**C**** _{k }**is the combination of

**k**things drawn from

**K**things. The formula for combination is,

_{K}C_{k }= 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)**

**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**.

**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.

**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**.

**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**