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

Get FREE Advanced Excel Exercises with Solutions!

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

Read More: How to Do 6 Sigma Calculation in Excel 


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.

Sample Dataset to Calculate Sigma Level in Excel

📌 Steps:

  • First and foremost, click on cell C8 and insert the following formula to calculate the Defects per Unit (DPU).
=C6/C5

Calculating Defects per Unit

  • 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)

Calculating DPO to Calculate the Sigma Level

  • At this time, click on cell C10 and insert the following formula to get the Defects per Million opportunities (DPMO).
=C9*1000000

Calculating DPMO to Calculate the Sigma Level

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

Calculating the Rolled Throughout Yield

  • Finally, to calculate the sigma level, click on cell C11 and insert the following formula.
=-NORMSINV(C9)+1.5
  • Subsequently, hit the Enter key.

Calculated Sigma Level in Excel

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.

Sample Dataset to Calculate Sigma Level of Continuous Data

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

Calculating the CPU Level

  • Afterward, to calculate the CPL  value, click on cell C10 and insert the formula below.
=(C7-C5)/(3*C8)
  • Following, press the Enter key.

Calculating the CPL Value

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

Calculating the CPK Value to Calculate Sigma Level

  • Last but not least, click on cell C12 and insert the formula below to calculate the sigma level.
=3*C11

Calculated the Sigma Level in Excel

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!


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.


Related Article


<< Go Back to Calculate Sigma in ExcelExcel for Statistics | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo