Mashhura Jahan
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.
Download Practice Workbook
You can download the practice workbook from here.
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,
Here,
X = Variable
P(X) = Probability of X
µ = Mean of the Probability Distribution
The formula for calculating μ is,
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.
- 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.
- 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.
Read More: How to Calculate Probability in Excel (3 Practical Examples)
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.
- 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.
Read More: Calculating Probability in Excel with Mean and Standard Deviation
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.
- 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.
Read More: How to Calculate Probability Distribution in Excel (with Quick Steps)
Similar Readings
- How to Use Normal Probability Distribution in Excel (3 Cases)
- How to Calculate Binomial Probability in Excel (with Easy Steps)
- Create Joint Probability Table in Excel (with Easy Steps)
- How to Use Continuous Probability Distribution in Excel
- How to Apply Weighted Probability in Excel (3 Useful Methods)
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.
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.
Read More: How to Calculate Empirical Probability with Excel Formula
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
- How to Model Uniform Probability Distribution in Excel (3 Ways)
- How to Get Simulation Probability in Excel (with Easy Steps)
- Poisson Probability Distribution in Excel (4 Practical Examples)
- How to Calculate Probability of Exceedance in Excel
- How to Find Marginal Probability in Excel (2 Simple Ways)
- Exponential Probability Distribution in Excel (2 Suitable Examples)
