If you are looking for a way to calculate 2 sigma then you have come to the right place. The main objective of this article is to explain how to calculate 2 Sigma in Excel. For today’s article, I am using Office 365. But, you can use any other version.
What Is 2 Sigma?
Sigma refers to the Population Standard Deviation. The formula for Sigma is:
Where,
N = Population Size
µ = Population Mean
x = Each Value from Population
So, the formula for 2 Sigma becomes:
How to Calculate 2 Sigma in Excel: 2 Easy Ways
I have taken the following dataset for this article. It contains the Name and Obtained Marks of some students. I will use this dataset to calculate 2 Sigma for the Obtained Marks in 2 easy ways.
1. Use Generic Formula to Determine 2 Sigma in Excel
In this first method, I will use the generic formula to calculate 2 Sigma in Excel. For this, I will find out Population Size (N), Population Mean (µ), x-µ, and (x-µ)2. And then I will calculate Sigma and 2 Sigma from these. Let’s explore the steps.
Step-01: Calculate Population Size
To begin with, I will calculate the Population Size (N).
- Firstly, select the cell where you want to calculate the Population Size. Here, I selected Cell D12.
- Secondly, in Cell D12 write the following formula.
=COUNT(C5:C10)
- Thirdly, press Enter and you will get the Population Size (N).
Step-02: Find Population Mean
Here, I will find the Population Mean for the dataset. I will need this later to calculate 2 Sigma in Excel.
- In the beginning, select the cell where you want the Population Mean.
- Then, write the following formula in that selected cell.
=AVERAGE(C5:C10)
- In the end, press Enter to get the result.
Step-03: Determine x-µ
In this step, I will determine x-µ for every student.
- First, select the cell where you want to calculate x-µ. Here, I selected Cell D5.
- Then, in Cell D5, write the following formula.
=C5-$D$13
- Next, press Enter to get the result.
- After that, drag the Fill Handle down to copy the formula.
- Finally, you can see that I have copied the formula to the other cells and got the desired output.
Step-04: Calculate (x-µ)^2
Now, I will calculate the (x-µ)2 for each student.
- In the beginning, select the cell where you want the (x-µ)2.
- Next, write the following formula in that selected cell.
=D5^2
- After that, press Enter to get the result.
- Further, drag the Fill Handle down to copy the formula to the other cells.
Step-05: Find Sigma
In this step, I will find Sigma for the Obtained Marks which is the standard deviation.
- Firstly, select the cell where you want the Sigma. Here, I selected Cell D14.
- Secondly, in Cell D14 write the following formula.
=SQRT(SUM(E5:E10)/D12)
- Thirdly, press Enter to get the Sigma.
🔎 How Does the Formula Work?
- SUM(E5:E10): Here, in the SUM function I selected cell range E5:E10 as numbers. The formula returns the summation of the cell range E5:E10.
- SUM(E5:E10)/D12: Now, this formula divides the summation by D12.
- SQRT(SUM(E5:E10)/D12): Finally, the SQRT function returns the square root of the result.
Step-06: Calculate 2 Sigma
Here, I will calculate 2 Sigma in Excel.
- First, select the cell where you want the 2 Sigma.
- Then, write the following formula in that selected cell.
=2*D14
- Lastly, press Enter and you will get the 2 Sigma.
Read More: How to Calculate Sigma Level in Excel
2. Apply STDEV.P Function to Calculate 2 Sigma in Excel
You can also use the STDEV.P function to calculate 2 Sigma in Excel. The STDEV.P function has been introduced in Excel 2010 and is available for all other versions after that. In Excel 2007 you will find this function as STDEVP. Let’s see the steps of the calculation.
Steps:
- Firstly, select the cell where you want the Sigma.
- Secondly, write the following formula in that selected cell.
=STDEV.P(C5:C10)
- Thirdly, press Enter to get the result.
- Next, select the cell where you want the 2 Sigma. Here, I selected Cell C13.
- Then, in Cell C13 write the following formula.
=2*C12
- Finally, press Enter to get the 2 Sigma.
Read More: How to Do 6 Sigma Calculation in Excel
Practice Section
Here, I have provided a practice sheet for you to practice how to calculate 2 Sigma in Excel.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
To conclude, I tried to explain how to calculate 2 Sigma in Excel in 2 easy and effective ways. I hope this article was helpful to you. If you have any questions, feel free to let me know in the comment section below.
