What Is the 2 Sigma?
Sigma refers to the Population Standard Deviation. The formula is:
Sigma = √(x-µ)2/N
N = Population Size
µ = Population Mean
x = Each Value from Population
The formula for 2 Sigma becomes:
2 Sigma = 2 * Sigma
The dataset contains students’ Names and Obtained Marks.
Method 1 – Use a Generic Formula to Determine the 2 Sigma in Excel
Step 1: Calculate Population Size
- Select a cell to calculate the Population Size. Here, D12.
- In D12 enter the following formula.
=COUNT(C5:C10)
- Press Enter to see the Population Size (N).
In the COUNT function, C5:C10 is selected as values. The formula returns the number of entries in the selected range.
Step 2: Find Population Mean
- Select a cell to see the Population Mean.
- Enter the following formula in that cell.
=AVERAGE(C5:C10)
- Press Enter to see the result.
In the AVERAGE function, C5:C10 is selected as numbers. The formula returns the average of these numbers.
Step 3: Determine x-µ
- Select a cell to calculate x-µ. Here, D5.
- In D5, enter the following formula.
=C5-$D$13
- Press Enter to see the result.
The formula subtracts the Population Mean from the Obtained Marks. An Absolute Cell Reference for Population Mean was used, so that it remains fixed while using Autofill.
- Drag down the Fill Handle to see the result in the rest of the cells.
This is the output.
Step 4: Calculate (x-µ)^2
- Select a cell to see the (x-µ)2.
- Enter the following formula.
=D5^2
- Press Enter to see the result.
The formula raises the value in D5 to the power of 2.
- Drag down the Fill Handle to see the result in the rest of the cells.
Step 5: Find the Sigma
- Select a cell to see the Sigma. Here, D14.
- Enter the following formula.
=SQRT(SUM(E5:E10)/D12)
- Press Enter to see the Sigma.
Formula Breakdown
- SUM(E5:E10): In the SUM function E5:E10 is selected as numbers. The formula returns the summation of E5:E10.
- SUM(E5:E10)/D12: This formula divides the summation by D12.
- SQRT(SUM(E5:E10)/D12): The SQRT function returns the square root of the result.
Step 6: Calculate the 2 Sigma
- Select a cell to see the 2 Sigma.
- Enter the following formula in the selected cell.
=2*D14
- Press Enter and you will see the 2 Sigma.
The formula multiplies the Sigma by 2 and returns the result.
Read More:How to Calculate Sigma Level in Excel
Method 2 – Applying the STDEV.P Function to Calculate the 2 Sigma in Excel
Steps:
- Select the cell to see the Sigma.
- Enter the following formula.
=STDEV.P(C5:C10)
- Press Enter to see the result.
In the STDEV.P function, C5:C10 is selected as numbers. The formula returns the Standard Deviation in the range: the Sigma.
- Select a cell to see the 2 Sigma. Here, C13.
- Enter the following formula.
=2*C12
- Press Enter to see the 2 Sigma.
The formula will multiply the Sigma by 2.
Practice Section
Practice here.
Download Practice Workbook
Download the practice workbook.
Related Article
<< Go Back to Calculate Sigma in Excel | Excel for Statistics | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
