One of the issues that we continue to receive a lot of questions and e-mail about is finding a standard normal table (z value table) that goes to six(6) standard deviations or more. The easy answers follow.
The easiest answer to the standard normal distribution table is to use the functions within Excel to develop your own standard normal distribution table. =NORMSDIST(Z) is for a normal distribution with a mean of 0 and standard deviation of 1.0. When using this function for a given sigma value Z it will return the area under the standard normal distribution curve from a negative infinity to the Z value.
From this you can construct any kind of standard normal distribution table ( z table) you want. We have developed a number of columns for the standard normal distribution curve. Our standard normal distribution table can be downloaded standard normal table.
Reading the table:
Column A in the standard normal distribution table is the sigma or Z value in increments of 0.1 from 0 to 7.5. You can make your increments larger or smaller and if you want more than 7.5 standard deviations just add to the level desired.
Column B in the standard normal distribution table is the area under the standard normal distribution curve from a negative infinity to the sigma value (Z). Since we started with Z=0 that is 50% of the area under the curve. Remember the curve is symmetric about the mean ( 0 ). If you want the area from a negative infinity to a negative Z value, you must find the corresponding positive Z and subtract from 1.0
For example the area under the curve from negative infinity to Ė1.0 standard deviations from the mean is: 1.0 - 0.8413= 0.15866
this is exactly the same area as from +1 standard deviation above the mean
to positive infinity, the symmetry still works.
Of course in Excel you can start with the negative standard deviations and increment toward zero if you want.
Column C in the standard normal distribution table is the area under the standard normal distribution curve from the sigma value (Z) to positive infinity. Using the example for 1.0 SD from above the area is 0.15866. If you need the area under the curve from a negative Z to positive infinity take the corresponding positive Z value and subtract that area from 0.5 then add the result to 0.5. For example from Z= -1.1 to positive infinity (0.5-0.135666) + 0.5 = 0.864334 or you could use column A realizing that the distribution is symmetric.
Column D in the standard normal distribution table is the area under the standard normal distribution curve from ĖZ to +Z. This is the area around zero from plus or minus the SD stated. For plus or minus 1.5 SD the area is 0.8664.
Column E in the standard normal distribution table is the area under the standard normal distribution curve outside of -Z to +Z. This is 1.0 minus the values for Column D. For outside plus or minus 1.5 SD the value is 0.1336.
Note that 0.8664 + 0.1336 = 1.0
This is the appropriate value with a centered process to find out the fraction defective for a given sigma level. Note that at 6 sigma it is 1.98 or almost 2 parts per billion. Column C shows that have of that is on the high side and half on the low side.
Column F in the standard normal distribution table we have converted the fraction to parts per million (PPM).
Now we will address the part that seems to cause a lot of people confusion. In six sigma the mean is allowed to move 1.5 SD. Explaining why 1.5 SD was chosen is beyond the scope of this newsletter. Those that take our Black Belt training learn this secret. Most people agree that no process stays centered directly on the target value. For the standard normal distribution this centered value is 0. The actual averages with any real process move a little to one side of zero to the other. Allowing the mean to shift 1.5 SD means that the calculation is no longer for a symmetric distribution. If the mean shifts up by 1.5 SD then it is 7.5 SD from the lower spec ( 4.5 SD from the upper spec) and similarly if it shift down it is 7.5 SD from the upper specification (4.5 SD from the lower spec). It canít do both at the same time so instead of a centered process where we consider both tails of the distribution, it becomes a single sided test where only one tail, 4.5 SD, of the distribution need be addressed. The amount beyond 7.5 SD from the mean is so small that it is neglected.
Column G in the standard normal distribution table shows the area under the standard normal distribution curve from a negative infinity to the sigma value shifted 1.5 SD
Column H in the standard normal distribution table shows the area under the standard normal distribution curve from the sigma value shifted 1.5 SD to positive infinity.
Column I in the standard normal distribution table converts the Column H values to parts per million. Note that at 6 sigma there are 3.4 PPM. Look at Column C from the sigma level to positive infinity, at 4.5 sigma the values is 3.4 PPM. Column F at 4.5 sigma show 6.8 PPM (half on the lower tail and half on the upper tail.).
We have highlighted a couple of the sigma values (z) that are of special interest. Between 3 sigma and 4 sigma are typical North American organizations. At 4.5 sigma without the shift you will find the same values as 6 sigma when the shift is considered. (6.0-1.5=4.5) be sure to confirm a single sided test or a double sided test.
We hope the standard normal distribution table from Excel is helpful. Remember you can make your own standard normal distribution table if you donít like the layout etc of ours. A last bit of warning, Excel is not perfect and there are some round off errors. For most of us these are not significant enough to worry about. Anyone who needs more accuracy that provided by Excel is likely a professional statistician. These data for a standard normal distribution table will be more than adequate for most Black Belt applications.
Finally Excel has a number of math and statistical functions. We have focused on only one of these, NORMSDIST(Z). You might find it useful to investigate others.
Cary W. Adams
10A Bayou RD
Lake Jackson, TX 77566