We have the Student Name in column B, ID in column C, and Mark in column D. We’ll calculate the sigma value for marks.
Method 1 – Calculating Sigma in Excel Manually
Steps:
- Insert the following formula in cell D11.
=AVERAGE(D5:D9)
- Press Enter and you will get the result for this cell.
- Insert the following formula in cell E5.
=D5-$D$11
- Use the Fill Handle to apply the formula to all cells.
- You will get the result for this column.
- Enter the following formula in cell F5.
=E5^2
- Use the Fill Handle to apply the formula to all cells.
- Here’s the result.
- Insert the following formula in cell D12.
=SUM(F5:F9)
- You will get the result for this cell.
- Insert the following formula in cell D13.
=SQRT(D12/COUNT(D5:D9))
- That’s the sigma value.
Method 2 – Using the STDEVP Function to Calculate Sigma in Excel
Steps:
- Insert the following formula in cell D11.
=STDEVP(D5:D9)
- Press Enter and you will get the final result.
Method 3 – Calculating Sigma for Distributed Frequency
Steps:
- Arrange the dataset similarly to the below image. We have Year in column B, Runs in column C, and Number of Batters in column D.
- Insert the following formula in cell E5.
=C5*D5
- Use the Fill Handle to apply the formula to all cells in the column.
- You will get the result for the whole column.
- Insert the following formula in cell C13.
=SUM(E5:E11)/SUM(D5:D11)
- Press Enter and you will get the result for this cell.
- Insert the following formula in cell F5.
=D5*(C5-$C$13)^2
- Press Enter.
- Use the Fill Handle to apply the formula to all cells.
- Insert the following formula in cell C14.
=SUM(F5:F11)/SUM(D5:D11)
- Press Enter and you will get the result for this cell.
- Insert the following formula in cell C15.
=SQRT(C14)
- You will get the final result.
Read More: How to Do 6 Sigma Calculation in Excel
Download the Practice Workbook
Calculate Sigma in Excel: Knowledge Hub
<< Go Back to Excel for Statistics | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
