# How to Calculate Variance of Probability Distribution in Excel

If you are looking for a way to calculate the variance of probability distribution in Excel then you came to the right place. The focus of this article is to explain how to calculate the variance of probability distribution in Excel.

## Formula for Calculating Variance of Probability Distribution

You can calculate the variance of a probability distribution by using its formula. The formula for the variance of a probability distribution is,

Variance = ∑(X – μ)2 * P(X)

Here,

X = Variable

P(X) = Probability of X

µ = Mean of the Probability Distribution

The formula for calculating μ is,

μ = ∑X * P(X)

## 2 Suitable Examples to Calculate Variance of Probability Distribution in Excel

To explain this article, I have taken the following dataset. This dataset contains the Number of Failed Students which is the variable (X) here. And the Frequency of the variable. I will use this dataset to calculate the variance of probability distribution in Excel. ### Example-01: Calculate Variance in Excel When Probability Is Not Provided

In this first method, I will calculate the variance of probability distribution in Excel when the probability is not given. I will use the generic formula to calculate it. Let’s see the steps.

#### Step-01: Calculate Probability

To begin with, I will calculate the Probability of X.

• Firstly, select the cell where you want to calculate the Total frequency. Here, I selected cell C13.
• Secondly, in cell C13 write the following formula.
`=SUM(C6:C12)` • Thirdly, press Enter to get the Total. Here, the SUM function returns the summation of the cell range C6:C12.
• After that, select the cell where you want to calculate the Probability. Here, I selected cell D6.
• Next, in cell D6 write the following formula.
`=C6/\$C\$13` • Then, press Enter to get the Probability. Now, the value in cell C6 is divided by the value in cell C13. Here, I used the Absolute Cell Reference for cell C13 so that the formula doesn’t change while using Autofill.
• Afterward, drag the Fill Handle to copy the formula to the other cells. • Finally, you can see that I have copied the formula to all the other cells and got the Probability. #### Step-02: Determine Mean of Probability Distribution

In this step, I will show you how you can calculate the Mean of probability distribution in Excel. For this, I will calculate X*P(X) first and then sum all of the X*P(X) values to get the Mean.

• In the beginning, select the cell where you want to calculate the X*P(X). Here, I selected cell E6.
• Next, in cell E6 write the following formula.
`=B6*D6` • Then, press Enter to get the result. Here, the formula multiplies the value in cell B6 by the value in cell D6.
• After that, drag the Fill Handle down to copy the formula to the other cells. • Next, you can see that I have copied the formula to the other cells and got all X*P(X) values. • Further, select the cell where you want the Mean. Here, I selected cell C15.
• Then, in cell C15 write the following formula.
`=SUM(E6:E12)` • In the end, press Enter and you will get the Mean. Here, in the SUM function, I selected cell range E6:E12 as numbers. The formula returns the summation of the cell range E6:E12.

#### Step-03: Calculate Variance of Probability Distribution in Excel

Now, I will calculate the Variance of a probability distribution in Excel. To begging with, I will calculate the (X – μ)2*P(X). Then, I will sum the values to get the Variance.

• First, select the cell where you want to calculate (X – μ)2*P(X).
• Then, write the following formula in that selected cell.
`=((B6-\$C\$15)^2)*D6` • After that, press Enter. Here, the value in cell C15 is subtracted from the value in cell B6 and the result is raised to the power of 2. And then, the final result is multiplied by the value in cell D6.
• Next, drag the Fill Handle down to copy the formula. • Consequently, you can see that I have copied the formula to the other cells. • Further, select the cell where you want to calculate the Variance of the probability distribution. Here, I selected cell C16.
• Then, in cell C16 write the following formula.
`=SUM(F6:F12)` • Finally, press Enter to get the Variance of a probability distribution. Here, in the SUM function, I selected cell range F6:F12 as numbers. The formula returns the summation of the cell range F6:F12.

### Example-02: Use SUMPRODUCT Function to Get Variance of Probability Distribution in Excel

Here, I will use the SUMPRODUCT function to calculate the Variance of probability distribution in Excel. For this example, I have taken the following dataset. It contains the Number of Failed Students which is X. And the Probability of X which is P(X). I will calculate the Variance of a probability distribution for this dataset. Let’s see the steps. #### Step-01: Calculate Mean in Excel

First, I will calculate the Mean of the probability distribution in Excel.

• In the beginning, select the cell where you want to calculate the Mean. Here, I selected cell C14.
• Then. in cell C14 write the following formula.
`=SUMPRODUCT(B6:B12,C6:C12)` • In the end, press Enter and you will get the meaning. Here, in the SUMPRODUCT function, I selected cell range B6:B12 as array1, and cell range C6:C12 as array2. Now, the formula returns the sum of the products of these 2 arrays.

#### Step-02: Determine Variance of Probability Distribution Excel

Now, I will calculate the Variance of the probability distribution in Excel using the SUMPRODUCT function.

• Firstly, select the cell where you want to calculate the Variance.
• Secondly, write the following formula in that selected cell.
`=SUMPRODUCT((B6:B12-C14)^2,C6:C12)` • Finally, press Enter to get the Variance of probability distribution. 🔎 How Does the Formula Work?

• (B6:B12-C14)^2: Here, the value in cell C14 is subtracted from the cell range B6:B12. And then the array is raised to the power of 2.
• SUMPRODUCT((B6:B12-C14)^2,C6:C12): Now, the SUMPRODUCT function returns the sum of the products of these 2.

## Practice Section

Here, I have provided a practice sheet for you to practice calculating the variance of probability distribution in Excel. ## Conclusion

In this article, I tried to cover how to calculate the variance of probability distribution in Excel. Here, I explained 2 different examples. I hope this article was clear to you. 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  5 Excel Tips
You Never Knew  