How to Calculate Variance of Probability Distribution in Excel

Get FREE Advanced Excel Exercises with Solutions!

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)


How to Calculate Variance of Probability Distribution in Excel: 2 Suitable Examples

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.

Dataset to Calculate 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.
  • Next, in cell C13 write the following formula.
=SUM(C6:C12)

Calculate Variance of Probability Distribution in Excel When Probability Is Not Provided

  • Then, 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

Calculating Probability of Variance of Probability Distribution in Excel

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

Dragging Fill Handle to Copy the Formula for Probability to find Variance of Probability Distribution in Excel

  • Finally, you can see that I have copied the formula to all the other cells and got all the probabilities.


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

Determine Mean of Probability Distribution in Excel

  • Then, press Enter to get the result.

Calculating Mean to get Variance of Probability Distribution in Excel

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)

Using SUM Function to Calculate Mean for Variance of Probability Distribution in Excel

  • 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 begin 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

Calculating Variance of Probability Distribution in Excel

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

Read More: Exponential Probability Distribution in Excel


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.

Dataset fot Using SUMPRODUCT Function to Get Variance of Probability Distribution in Excel


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)

Using SUMPRODUCT Function to Calculate Mean for Variance of Probability Distribution in Excel

  • 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)

Determine Variance of Probability Distribution Excel using SUMPRODUCT Function

  • 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. 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 Use Continuous Probability Distribution in Excel


Practice Section

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

Practice Sheet for Variance of Probability Distribution in Excel


Download Practice Workbook

You can download the practice workbook from here.


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


<< Go Back to Excel Probability Distribution | Excel Probability | Excel for StatisticsLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo