This tutorial will demonstrate how to calculate sigma in Excel. The term Sigma represents the Standard Deviation which is a measurement of the scattering of a set of values from their mean. If the Standard Deviation of a set of values is high, we can say that the data highly deviates from its mean or average. And thus, we can say that those data are not similar in nature, or they are independent. If the Standard Deviation is low, we can say that the data stays close to its mean and there is a greater possibility of them being related to each other. So, it is very important to learn how to calculate sigma in Excel.
3 Effective Methods to Calculate Sigma in Excel
We’ll use a sample dataset overview as an example in Excel to understand easily. For instance, we have the Student Name in column B, Id in column C, and Mark in column D. If you follow the steps correctly, you should learn how to calculate sigma in Excel on your own. The methods are
1. Calculating Sigma in Excel Manually
In this case, our goal is to learn how to calculate sigma in Excel manually. We can do this by following the below steps.
Steps:
- First, insert the following formula in cell D11.
=AVERAGE(D5:D9)
The formula will return the average number using the AVERAGE function.
- Next, press Enter, and you will get the result for this cell.
- After that, insert the following formula in cell E5.
=D5-$D$11
- Subsequently, you will get the result for this cell and then use the Fill Handle option to apply the formula to all cells.
- After that, you will get the result for this column.
- Next, insert the following formula in cell F5.
=E5^2
- Moreover, you will get the result for this cell and then use the Fill Handle option to apply the formula to all cells.
- Afterward, you will get the result accordingly.
- Then, insert the following formula in cell D12.
=SUM(F5:F9)
The formula uses the SUM function to calculate the sum of the squares of the difference between the average and each data.
- As a result, you will get the result for this cell.
- Now, insert the following formula in cell D13.
=SQRT(D12/COUNT(D5:D9))
Finally, we determine the sigma using the SQRT and COUNT functions.
- Lastly, you will get the final result accordingly.
Therefore, we have learned how to calculate the sigma in Excel manually.
2. Using STDEVP Function to Calculate Sigma in Excel
Now, we want to learn how to calculate sigma in Excel by using the STDEVP function with the help of the following steps.
Steps:
- To begin with, insert the following formula in cell D11.
=STDEVP(D5:D9)
- In addition, press Enter and you will get the final result accordingly.
Hence, we have achieved to learn how to calculate sigma in Excel by using the STDEVP function
3. Calculating Sigma for Distributed Frequency
At this point, our goal is to learn how to calculate sigma in Excel in the case of two variables for distributed frequency. In this case, we will calculate the sigma for distributed frequency by following the below steps.
Steps:
- Firstly, arrange the dataset similarly to the below image. In this case, we have Year in column B, Runs in column C, and Number of Batters in column D.
- Then, insert the following formula in cell E5.
=C5*D5
- Next, you will get the result for this cell and then use the Fill Handle option to apply the formula to all cells.
- As a result, you will get the result for the whole column.
- Subsequently, insert the following formula in cell C13.
=SUM(E5:E11)/SUM(D5:D11)
- Moreover, press Enter and you will get the result for this cell.
- Furthermore, insert the following formula in cell F5.
=D5*(C5-$C$13)^2
- Afterward, you will get the result for this cell and then use the Fill Handle option to apply the formula to all cells.
- Now, you will get the result for this cell and then use the Fill Handle option to apply the formula to all cells.
- Thereafter, insert the following formula in cell C14.
=SUM(F5:F11)/SUM(D5:D11)
- Addition with, press Enter and you will get the result for this cell.
- Coming to the last section, insert the following formula in cell C15.
=SQRT(C14)
- Lastly, you will get the final result accordingly.
So, at last, we have managed to learn how to calculate sigma in Excel in the case of two variables for distributed frequency.
Read More: How to Do 6 Sigma Calculation in Excel
Things to Remember
- We have shown the steps of determining sigma in 3 methods. The foremost thing is to learn about the whole formula and how it works. We believe if you understand the formula, then achieving the outcome with Excel becomes insanely easy.
- While inserting the formula, we must put extra conscious. Any mistake in the formula will give us wrong results.
- In the first and third methods, we have also determined the Average. So, if anyone wants to determine the Arithmetic Mean, he can easily use it to do it.
- Among all the methods, the second method is the most efficient one.
- Therefore, we request to download the Excel file and use it while creating your own datasheet.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
Henceforth, follow the above-described methods. These methods will help you to learn how to calculate sigma in Excel. We will be glad to know if you can execute the task in any other way. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.
Calculate Sigma in Excel: Knowledge Hub
<< Go Back to Excel for Statistics | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
