How to Calculate 2 Sigma in Excel (2 Easy Ways)

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:

Sigma = √(x-µ)²/N

Where,

N = Population Size
µ = Population Mean
x = Each Value from Population

So, the formula for 2 Sigma becomes:

2 Sigma = 2 * Sigma

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-µ)². 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).

Here, in the COUNT function, I selected cell range C5:C10 as values. Now, the formula will return the number of entries in the selected range.

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.

Here, in the AVERAGE function, I selected cell range C5:C10 as numbers. Now, the formula will return the average of these numbers.

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.

Here, the formula subtracts the Population Mean from the Obtained Marks. I used Absolute Cell Reference for Population Mean. So that it remains fixed while using Autofill.

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-µ)² for each student.

In the beginning, select the cell where you want the (x-µ)².
Next, write the following formula in that selected cell.

=D5^2

After that, press Enter to get the result.

Now, the formula raises the value in Cell D5 to the power of 2.

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.

Here, the formula multiplies the Sigma by 2 and returns it as result.

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.

Here, in the STDEV.P function, I selected cell range C5:C10 as numbers. The formula will return the Standard Deviation of the given cell range. Which is the Sigma.

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.

Here, the formula will multiply the Sigma by 2.

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.

Calculating 2 Sigma.xlsx

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.

How to Do 6 Sigma Calculation in Excel

<< Go Back to Calculate Sigma in Excel | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!