How to Calculate Sigma Level in Excel (2 Suitable Ways)

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.

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.

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.5

How to Calculate Sigma Level in Excel: 2 Suitable Ways

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.

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.

Download Practice Workbook

You can download our practice workbook from here for free!

Calculate Sigma Level.xlsx

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.