How to Do 6 Sigma Calculation in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

6 sigma, also known as process sigma, is an important factor when it comes to the production process. Obviously, you have heard this term if you are a production specialist or working in the industrial sector. In this article, we’ll demonstrate 2 easy and quick methods on how to do 6 sigma calculation in Excel. So, let’s go through the entire article to understand the topic properly.


What Is 6 Sigma?

There are differences of opinion regarding 6 Sigma. Some claim that it is a quantitative and analytical symbol. It is referred to as an organizational strategy by others. And many assert that it is a paradigm for development. They’re all accurate.
In many organizations, the term “6 Sigma” refers to a quality measurement that aims for absolute flawlessness. 6 Sigma is a methodical, computation approach for reducing errors in any process, from production to procedural, from goods to servicing, by aiming for six standard deviations between the mean and the closest specification limit.


Importance of 6 Sigma in Production

Although individual outcomes may have varied, there are a basic set of benefits that all firms that have adopted 6 sigma should have attained. They are as follows:

  • Increased efficaciousness, performance, and productivity.
  • Enhanced product and service quality, as well as a decrease in waste.
  • Decreased operational costs and volatility in the process.
  • Improved client satisfaction and ambitious edge.
  • Greater interaction and involvement of employees.

How to Do 6 Sigma Calculation in Excel: 2 Methods

The calculation of 6 sigma involves two different approaches. The first one is when the data is discrete. And another one is for continuous data. We’ll discuss these two types in the following part of the article. So, let’s be with us.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


1. Calculation of 6 Sigma When Data Are Discrete

In our first method, we’ll exhibit how you can do 6 sigma calculation for discrete data in Excel. Discrete data is a sort of quantitative value that consists of entire, concrete numbers with predetermined, set values that can be counted. So, let’s follow us carefully.

📌 Steps:

Here, we have got the Number of Units, Number of Opportunities, and Total Number of Defects in cells in the C4:C6 range.

Calculation of 6 Sigma when Data is Discrete

Here, U, O, and D represent the Number of Units, the Number of Opportunities, and the Number of Defects correspondingly.
Now, we have to calculate the DPO which is defects per opportunity from the previous data. The generic formula to find it is the following.

DPO = D / ( U * O )

To do the task in Excel,

  • Firstly, create an output range in cell C8.

  • Secondly, select cell C8 and enter the following formula in the cell.
=C6/(C4*C5)
  • Then, press the ENTER key.

Calculation DPO to get 6 sigma in Excel

The universal formula to get the value of 6 Sigma from DPO is the following.

6 Sigma = – NORMSINV (DPO) + 1.5

So, we can easily replicate this in Excel.

  • Thirdly, go to cell C10 and insert the formula below.
=-NORMSINV(C8)+1.5

The NORMSINV function returns the inverse of the normal distribution.

  • After that, hit ENTER.

6 sigma calculation in excel

That’s how we can calculate the 6 sigma value for discrete data.


2. Calculation of 6 Sigma When Data Are Continuous in Excel

In the previous method, we saw the approach to find out the value of 6 sigma for discrete data. But, in this example, we’ll do the calculation for a continuous dataset.
Here, we are using a report on the Size of Samples of ABC Corporation. This dataset includes the Sample numbers and their corresponding Sizes in columns B and C respectively.

Calculating 6 Sigma when Data is Continuous

Note that this is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.
Now, we’ll use this dataset to do the 6 sigma calculation in Excel. It’s simple & easy, just follow along. So, let’s begin.

📌 Steps:

At first, we mentioned the LSL and USL for this dataset. These two terms mean Lower Specification Limit and Upper Specification Limit subsequently. Here, the ideal size is 32. We presumed that the purchaser is inclined to acknowledge the product with a size variation of +/-1. So the LSL and USL should be 31 and 33.

LSL and USL of dataset

  • Then, select cell F7 and write down the following formula.
=AVERAGE(C5:C24)

Using the AVERAGE function, we calculated the Mean of this data. Here, C5:C24 represents the entire data range.

  • Following this, tap the ENTER key.

Calculation of Mean

  • Currently, go to cell F8 and paste the formula below.
=STDEV(C5:C24)

The STDEV function calculates the standard deviation of any dataset.

  • As always, press ENTER.

Calculation of standard deviation of dataset

Forthwith, we calculate Cpu and Cpl for this dataset. Based on the system’s upper specification limit, the Cpu is an indicator of its conceivable competence. This goes the same for Cpl but it’s just based on the lower specification limit, that’s the difference. So, see below to learn to calculate these two terminologies.

  • At first, select cell F10 and enter the following formula.
=(F5-F7)/F8
  • After that, press ENTER.

So, the value of Cpu is 0.07263.

  • Again, go to cell F11 and put down the formula below.
=(F7-F4)/F8
  • As usual, tap ENTER.

The 6 Sigma Value would be the minimum value between the values of Cpu and Cpl divided by the standard deviation.

  • So, navigate to cell F13 and insert the following formula.
=MIN(F10:F11)/F8

The MIN function returns the minimum value from a range of cells.

  • As always, hit the ENTER key.

Calculation of 6 Sigma when Data is Continuous

Finally, we got the value of 6 sigma for this continuous data.

Read More:How to Calculate Sigma Level in Excel


How to Calculate 3 Sigma in Excel

As a bonus, we’ll show how you can calculate 3 sigma values in Excel. To do this, we’ll use the same dataset. So, without further delay, let’s see how to do it.

📌 Steps:

  • First of all, calculate the mean like before.
  • Then, select cell F5 and write down the following formula in the cell.
=VAR(C5:C24)

The VAR function returns the variance of a sample taken from population data.

  • Afterward, press ENTER.

How to Calculate 3 Sigma in Excel

  • Later, select cell F6 and paste the following formula.
=SQRT(F5)

The SQRT function returns the square root of any number. Actually, we are finding out the standard deviation from the square root of the variance.

  • Next, press ENTER.

calculating 3 Sigma in Excel

  • Presently, go to cell F8 and introduce the formula below.
=3*F6
  • As usual, press ENTER.

Calculation of 3 Sigma in Excel

Basically, it’s equal to thrice the standard deviation.

  • Now, add the Mean value with the 3 Sigma Value to get High End Value.
=F8+F4

  • Additionally, you can extract the Low End Value by subtracting the Mean from the 3 Sigma Value.
=F8-F4

Calculating 3 Sigma in Excel


Practice Section

For doing practice by yourself we have provided a Practice section like the one below in each sheet on the right side. Please do it by yourself.

Practice section


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Conclusion

This article explains how to do 6 sigma calculation in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.


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.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo