How to Graph UCL and LCL in Excel 2007
by James GreenGraphing the Upper Control Limit (UCL) and Lower Control Limit (LCL) is useful for assessing how far an observation deviates from its mean. Unfortunately, Microsoft Excel does not have a built-in function for this and you will have to graph them manually. In this example, five groups occupy rows 1 to 5 and their corresponding observations occupy columns A to D, so the entire observation set occupies cells A1 to D5. This example uses Excel 2007.
Calculate the mean of each group by typing "=AVERAGE(A1:D1)" (without quote marks) in cell F1. Press "Enter," then guide the cursor over the right hand corner of cell F1 until it changes to a cross-hair. Click and drag down to cell F5 and release to auto-fill the remaining means.
Calculate the mean of means by typing "=AVERAGE(F1:F5)" (without quote marks) in cell F7 and pressing "Enter."
Calculate the standard deviation of all observations by typing "=STDEV(A1:D5)" (without quote marks) in cell F8 and pressing "Enter."
Calculate the Upper Control Limit (UCL), which is the mean of means plus three times the standard deviation. In this example, type "=F7+3*F8" (without quote marks) in cell F9 and press "Enter."
Calculate the Lower Control Limit (LCL), which is the mean of means minus three times the standard deviation. In this example, type "=F7+3*F8" (without quote marks) in cell F10 and press "Enter."
Copy the mean of means in cell F7 and paste its value into cells A6 to D6. Repeat this step with UCL and LCL by pasting them into cell A7 to D7 and A8 to D8, respectively. This will ensure the final graph includes the mean of means, the UCL and the LCL.
Graph your observations. In this example, highlight cells A1 to D8, select the "Insert" tab, then the "Line" button, then select the basic 2-D line from the sub-menu. Right-click the graph, select the "Select Data..." option, and then select the "Switch Row/Column" button in the "Select Data Source" dialog box before clicking "OK".
Tip
Labeling cells will help you keep track of your calculations.
References
Photo Credits
- photo_camera keyboard image by Andrey Kurehin from Fotolia.com