Tanjim Reza
When thinking of industries, we are required to calculate our product variability, total defects, defect percentages, and so on. These calculations are a little bit different from our basic math calculations. Yet, we can achieve these results through Excel. In determining these industrial parameters, sigma level determination is the most important approach. In this article, I will show you 2 suitable ways to calculate the sigma level in Excel.
Download Practice Workbook
You can download our practice workbook from here for free!
What Is Sigma Level?
The Sigma level is basically a parameter to indicate the number of defects in 1 million opportunities. To make it more clear, the sigma level indicates that if any industry produces 1 million products, how many defective products would be there. There are 6 sigma levels in the definition. Each one of them indicates different ranges of defects per million opportunities.
- Sigma level 1: This means there will be 697,672 defects in 1 million opportunities.
- Sigma level 2: This indicates there will be around 308,770 defects in 1 million opportunities.
- Sigma level 3: This gives a hint that there would be around 66811 defects in 1 million opportunities.
- Sigma level 4: This means there would be 6210 defects approximately in 1 million opportunities.
- Sigma level 5: This tells us there would be only 233 defects in 1 million opportunities.
- Sigma level 6: This means there would be just 3.4 defects in 1 million opportunities.
Read More: How to Calculate 2 Sigma in Excel (2 Easy Ways)
6 Sigma Calculation Formulas in Excel
We need to know and find the values below to find if the sigma level is 6 or somewhat else.
- Unit (U): This is the number of products taken into consideration for inspection.
- Opportunity (O): This is the number of flaws possible in one unit.
- Defect (D): This is the number of products that contain any number of flaws.
- Defects per Unit (DPU): This is the ratio between the total number of defects and the total number of units.
DPU = ( D / U)- Defects per Opportunity (DPO): This is the average number of products per opportunity.
DPO = [D / (U*O)]- Defects per Million Opportunities (DPMO): This is the ratio of the number of defects in one million opportunities.
DPMO = (DPO * 1,000,000)- Rolled Throughout Yield (RTY): This is the percentage of products that is free of defects.
RTY % = (1-DPO)*100- Sigma Level: The most basic formula to calculate the Sigma Level in Excel is as follows:
Sigma Level = -NORMSINV(DPO)+1.5Read More: How to Do 6 Sigma Calculation in Excel (2 Easy Ways)
2 Suitable Ways to Calculate Sigma Level in Excel
1. Calculate Sigma Level for Discrete Data
Mostly, we need to calculate the sigma level from discrete data. In this case, we need the inputs of the Number of Units, Defects, and Number of Defect Opportunities per Unit.
Say, we have these inputs of an industry as 8000, 500, and 5 respectively in cells C5:C7. Now, we need to calculate the sigma level of this industry’s production scenario. We can do this with several calculations and the NORMSINV function. Follow the steps below to do this.
📌 Steps:
- First and foremost, click on cell C8 and insert the following formula to calculate the Defects per Unit (DPU).
=C6/C5
- Afterward, we need to calculate the Defects per Opportunity (DPO).
- To do this, click on cell C9.
- Following, insert the formula below and press the Enter key.
=C6/(C5*C7)
- At this time, click on cell C10 and insert the following formula to get the Defects per Million opportunities (DPMO).
=C9*1000000
- Afterward, we need to find the Rolled Throughout Yield percentage.
- In order to calculate this, click on cell C11 and insert the formula below.
=(1-C9)*100- Subsequently, press the Enter key.
- Finally, to calculate the sigma level, click on cell C11 and insert the following formula.
=-NORMSINV(C9)+1.5- Subsequently, hit the Enter key.
Thus, you will be able to calculate the sigma level for the respective industry’s production scenario.
Read More: How to Calculate Sigma in Excel (3 Effective Methods)
2. Calculate Sigma Level for Continuous Data
Another important approach to calculating the sigma level is to use continuous data. In this case, we will need the Lower specification Limit (LSL), Upper Specification Limit (USL), Mean (M), and Standard Deviation (STD) initially. And, you will need to find the CPU, CPL, CPK values from the inputs at first to calculate the Sigma Level.
Now, suppose, we have these data for the production of an industry as 75.1, 75.9, 75.4, and 0.05 respectively in cells C5:C8. Now, we need to calculate the sigma level for this production scenario of the industry. Follow the steps below to accomplish this.
📌 Steps:
- At the very beginning, click on cell C9 and insert the following formula to calculate the CPU value.
=(C6-C7)/(3*C8)- Subsequently, hit the Enter key.
- Afterward, to calculate the CPLÂ value, click on cell C10 and insert the formula below.
=(C7-C5)/(3*C8)- Following, press the Enter key.
- Now, you need to calculate the CPK value which is the minimum of CPL and CPU.
- So, click on cell C11 and insert the formula below.
=MIN(C9:C10)- Afterward, press the Enter key.
- Last but not least, click on cell C12 and insert the formula below to calculate the sigma level.
=3*C11
Thus, the sigma value for this production will be calculated as well.
Note:
You can also calculate the sigma level if you are given only one specification limit instead of both upper and lower. In that case, the given specification value’s CP will be the CPK value.
Read More: How to Insert Sigma Symbol in Excel (8 Easy Ways)
Conclusion
So, in this article, I have shown you two suitable ways to calculate the sigma level in Excel. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.
And, visit ExcelDemy to learn more things about Excel! Have a nice day!
