Calculating Sigma Levels in Excel

Last Updated on 10 September 2023

The goal of Six Sigma is of course for your organization to reach a Six Sigma level of defects, so how do you know when you’ve got there? There are plenty of lookup tables around in textbooks for you to use (make sure you get specifically a Six Sigma one, as most don’t go high enough for what we need). But what if you’re a bit of a perfectionist / geek like me and want to be in more control of your data? For that you need to calculate it yourself.

Luckily there’s some easy formulas in Excel that will do this for you.

Normal Distributions

Sigma levels are based on the normal distribution curve. The normal curve (or bell curve) is the standard distribution for random distributions of data around a central average, with no bias above or below. Many things follow normal distributions, such as heights of people, grades in an exam, and errors in a process.

Sigma level measures the proportion of this graph that doesn’t result in defects. Strictly speaking, it is the number of standard deviations that you can fit between the mean (peak) of the normal distribution and the Upper Specification Limit (the maximum reading that the customer will allow).

You therefore use the ‘NORMSINV’ and ‘NORMSDIST’ functions in Excel to find the Sigma level from DPMO (Defects Per Million Opportunities) or find DPMO from the Sigma level.

Converting Sigma Level to DPMO

If you’re aiming for a certain Sigma level, you’ll need to know how to convert from Sigma number to DPMO (defects per million opportunities) – this can be done by NORMSDIST() in older Excel, or NORM.S.DIST() in newer versions (NORMSDIST() should still work though).

NORMSDIST()

This formula ‘returns the standard normal cumulative distribution function. The distribution has a mean of 0 (zero) and a standard deviation of one. Use this function in place of a table of standard normal curve areas’.

What you’re trying to achieve is find the percentage of the population to the left of the 6 Sigma (six standard deviations) line, adjusted by 1.5 for the Six Sigma drift. The chart of ‘1.5 – Sigma level’ against NORMSDIST() looks like this:

From this we see that a Sigma level of 1.5 is where 50% of your sample are errors. We’ve used the left hand side of the bell curve (1.5-sigma level) as the formula is cumulative, so it will give the % outside the area.

Multiplying this by 1,000,000 gives the DPMO, so at 1.5 sigma level that’s a DPMO of 500,000. Zooming in on the right hand side of the graph and multiplying by 1,000,000 gives:

As the Sigma level goes to 6, you can see DPMO goes to 3.4, the familiar DPMO target. You can therefore use this function to go from Six Sigma level to DPMO.

So how do you actually calculate it? The general formula is:

DPMO = NORMSDIST(1.5 – σ)1,000,000
e.g. DPMO = NORMSDIST(1.5 – 6) 1,000,000 = 3.4

NORM.S.DIST()

NORMSDIST() has also been replaced in the latest versions of Excel, with the more flexible NORM.S.DIST() instead. This is very easy, as the new option is that you can choose whether you want cumulative or not cumulative; we want it cumulative so all you do is add a ‘true’ at the end. Our example then becomes:

DPMO = NORM.S.DIST(1.5 – σ,TRUE)1,000,000 e.g. DPMO = NORM.S.DIST(1.5 – 6,TRUE)1,000,000 = 3.4

Example

Say there you want to find the target defects allowed for the different Sigma levels up to 6. The process we’re looking at has 200 opportunities for defects in each product made. We’re looking at how many defects were found in a production run of 10,000.

As you can see, to reach e.g. 4 Sigma, the DPMO works out at 6,210. With 200 opportunities for defect per product, this allows 1,241,933 defects per million products made. In a production run of 10,000 this will be 12,419 defects; if you have fewer than 12,419 defects in the production run you’ve reached 4 Sigma. If you have 6 or fewer then you’re reached Six Sigma (look at F10 on the chart).

I’ve rounded the calculation down to zero decimal places in column F as if e.g. 5.8 defects are allowed, 6 will be a miss but 5 will be under this level – the number must therefore always be rounded downwards.

Converting DPMO to Sigma level

More frequently, you’ll have the DPMO and want to get to the Sigma level, to work out the level that you’re currently at. This works by doing the previous calculation in reverse.

NORMSINV()

This formula ‘returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one’. In Six Sigma world, this converts from a % of problems to a sigma level (and then you add on the 1.5 six sigma drift).

Sigma Level σ = ABS(NORMSINV(DPMO/1,000,000))+1.5
e.g. σ = ABS(NORMSINV(3.4/1,000,000))+1.5 = 6

Example

The most common issue is wanting to know what Sigma level your production is running at. To use an example, in a system of 50 opportunities for failure per product and a production run of 25,000 you have the following:

As you can see from the above, if you find 48 defects in the above setup, your sigma level is 5.45. You can set up your formulas and easily find out your Sigma level for any variation of inputs.

NORM.INV()

The above method has now been changed in newer versions of Excel by our formula being replaced with a new one, NORM.INV(). This is more flexible, and allows you to input a mean and standard deviation. We still want mean = 0 and standard deviation =1, so change NORMSINV(x) to NORM.INV(x,0,1).

Redoing our formula:

Sigma Level σ = ABS(NORMS.INV(DPMO/1,000,000,0,1))+1.5
e.g. σ = ABS(NORM.INV(3.4/1,000,000,0,1))+1.5 = 6

Calculating from Failure rate

If you want to calculate directly from failure rate instead of using DPMO:

Failure rate = defects / opportunities for defects

then it’s actually even easier – you just replace DPMO with failure rate and remove all the times there’s 1 million in the formula, making the formulas:

Calculating Sigma Levels in Excel

Normal Distributions