Oct
28
There are frequent questions on the discussion boards asking for things like a z table from -6 to +6 standard deviations – or a derivative of it a sigma table from 0 to 6 sigma (the old 1.5 sigma shift). I have also helped people through things like the CQE or CSSBB exams where they are carrying multiple books just to get access to statistical tables. With a little knowledge of Excel, you can create all of these things for your self.
Excel basics – all of what you will need is contained in the fx (mouseover and it is called paste function) button. When you click on it, you will be given several options, choose statistical. You will find multiple distributions covered there, certainly all of the ones used in Six Sigma training and loads you have probably never heard of. Think about how table are laid out, the convention in Excel is to call having a stat looking for a probability (distribution)dist. For example the standard normal where you are looking for a probability given a z value is called normsdist. The convention for having a probability looking for a stat (distribution)inv. For example, the standard normal distribution where you have a probability looking for the associated z value is normsinv.
Setting up a z (standard normal) table from -6 to +6 standard deviations –
1) Start in cell A1 and type in z value
2) In cell A2, type -6. In cell A3, type -5.9. Then highlight cell A2 and A3, go to the bottom right of cell A3 until the cursor becomes a small cross. Drag down to cell A122.
3) In cell B1, type .09. In cell C1, type .08. Then highlight B1 and C1, go to bottom right of cell C1 until the cursor becomes a small cross. Drag across to cell K1.
4) In cell B2, open fx by double clicking. Go to statistical, normsdist. And for the z value, type in $A2+B$1. Go to the bottom right hand corner of B2 until the cursor becomes a small cross and drag across to K2. Release the mouse and while cells B2 through K2 are still highlighted go to the bottom right of cell K2 until the cursor becomes a small cross and double click. That will fill out the whole table from -6.09 to +6.09.
5) All of the values listed are correct, but for clarity we need to reverse the column headings starting in row 64. Go to row 64, highlight both the 64 and 65 right click and insert. This will give you two blank rows.
6) In cell A65, type 0.
7) In cell A64, type z value. In cell B64, type .00. In cell C64, type .01. Then highlight B64 and C64, go to bottom right of cell C64 until the cursor becomes a small cross. Drag across to cell K64
Modify the formula in B66 to =NORMSDIST($A66+B$64). Go to the bottom right hand corner of B66 until the cursor becomes a small cross and drag across to K66. Release the mouse and while cells B65 through K65 are still highlighted go to the bottom right of cell K65 until the cursor becomes a small cross and first drag up to row 65. Release the mouse and while cells B65 through K66 are still highlighted go to the bottom right of cell K66 until the cursor becomes a small cross and double click. That will fill out the whole table from 0.01 to +6.09.
9) Label the tab for the worksheet z table.
Your table is done.
Setting up a Sigma Table table from 0 to 6 Sigma –
1) Start with a blank worksheet.
2) Start in cell A1 and type in Sigma value
3) In cell A2, type 0. In cell A3, type .01. Then highlight cell A2 and A3, go to the bottom right of cell A3 until the cursor becomes a small cross. Drag down to cell A62.
4) In cell B1, type 0. In cell C1, type .01. Then highlight B1 and C1, go to bottom right of cell C1 until the cursor becomes a small cross. Drag across to cell K1.
5) In cell B2, type in =(1-NORMSDIST($A2+B$1-1.5))*1000000. Go to the bottom right hand corner of B2 until the cursor becomes a small cross and drag across to K2. Release the mouse and while cells B2 through K2 are still highlighted go to the bottom right of cell K2 until the cursor becomes a small cross and double click. That will fill out the whole table from 0 to +6.09.
6) Label the tab of the worksheet Sigma value (1.5 sigma shift).
Your table is done.
Gary