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.
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. 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 DPMO to Sigma level
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
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 example:
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
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).
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’.
DPMO = NORMSDIST(1.5 – σ)*1,000,000
e.g. DPMO = NORMSDIST(1.5 – 6)*1,000,000 = 3.4
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
Calculating from Failure rate
If you want to calculate directly from failure rate:
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:
Sigma Level σ = ABS(NORMSINV(Failure rate))+1.5
Failure rate = NORMSDIST(1.5 – σ)
Sigma Level σ = ABS(NORMS.INV(Failure rate,0,1))+1.5
Failure rate = NORM.S.DIST(1.5 – σ,TRUE)