How to Do 6 Sigma Calculation in Excel

What Is 6 Sigma?

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.

The calculation of 6 sigma involves two different approaches. One for discrete data, and another for continuous data.

Method 1 – Calculation of 6 Sigma with Discrete Data

Steps:

The sample dataset showcases the Number of Units, Number of Opportunities, and Total Number of Defects in the C4:C6 range.

Here, U, O, and D represent the Number of Units, the Number of Opportunities, and the Number of Defects.
To calculate the DPO, which is defects per opportunity from the previous data, use this generic formula.

DPO = D / ( U * O )

Create an output range in C8.

Select C8 and enter the following formula.

=C6/(C4*C5)

Press ENTER.

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

6 Sigma = – NORMSINV (DPO) + 1.5

Go to C10 and enter the formula below.

=-NORMSINV(C8)+1.5

The NORMSINV function returns the inverse of the normal distribution.

Press ENTER.

Method 2 – Calculation of 6 Sigma With Continuous Data in Excel

This is a continuous dataset, including the Sample numbers and their corresponding Sizes in columns B and C.

Steps:

The LSL (Lower Specification Limit) and USL (Upper Specification Limit) were applied in this dataset. The ideal size is 32. A size variation of +/-1 is assumed. LSL and USL should be 31 and 33.

Select F7 and enter the following formula.

=AVERAGE(C5:C24)

Use the AVERAGE function to calculate the Mean. Here, C5:C24 represents the entire data range.

Press ENTER.

Go to F8 and use the formula below.

=STDEV(C5:C24)

The STDEV function calculates the standard deviation.

Press ENTER.

You calculate C_pu and C_pl for this dataset. Based on the system’s upper specification limit, the C_pu is an indicator of its conceivable competence. C_pl is based on the lower specification limit.