How to Calculate 2 Sigma in Excel - 2 Methods

What Is the 2 Sigma?

Sigma refers to the Population Standard Deviation. The formula for Sigma is:

Sigma = √(x-µ)²/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’ Name and Obtained Marks.

Method 1 – Use a Generic Formula to Determine the 2 Sigma in Excel

Find Population Size (N), Population Mean (µ), x-µ, and (x-µ)²and calculate Sigma and 2 Sigma.

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

Find the Population Mean for the dataset.

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-µ

Determine x-µ for every student.

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

Calculate the (x-µ)² for each student.

Select a cell to see the (x-µ)².
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

Find the Sigma for the Obtained Marks.

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.