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 is the proportion of this graph that results in errors. You therefore use the ‘NORMSINV’ and ‘NORMSDIST’ functions in Excel to find the Sigma level or find DPMO (Defects Per Million Opportunities) 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.

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:

## Summary

### Older Excel

Sigma Level σ = ABS(NORMSINV(Failure rate))+1.5

Failure rate = NORMSDIST(1.5 – σ)

DPMO = NORMSDIST(1.5 – σ) *1,000,000

### Newer Excel

Sigma Level σ = ABS(NORMS.INV(Failure rate,0,1))+1.5

Failure rate = NORM.S.DIST(1.5 – σ,TRUE)

DPMO = NORM.S.DIST(1.5 – σ,TRUE) *1,000,000

PREVIOUS POST:

Swimlane Diagrams

NEXT POST:

This is currently the last page. Please look to ‘Start Here‘ to find where to go next.

Hans Schulze says

I would love to see one or two examples, where you start with a known dataset or stddev, and step through the sequence of calculations. Thanks!

Rob says

Hi Hans,

Thanks for your comment. I’ve tried to improve the explanation of what it’s trying to achieve and added a couple of examples. Please let me know if it’s still unclear.

Thanks!

Rob