QI Macros Lesson 9 - Control Chart Limits | |||||||||||||||||||
We help people think! |
In this Issue
Control Chart Limit CalculationsGenerally, you calculate control limits using your first 20 to 25 data points and then you use those limits to evaluate the rest of your data. If you have a process change, you should recalculate your control limits beginning with data after the process change occurred. The purpose of this lesson is to let you know how the QI Macros work and to instruct you on how to change the control limit calculations if you want to change them. How Control Chart Limits are Calculated in the QI MacrosThe QI Macros formulas are consistent with Juran's Quality Control Handbook (4th), Montgomery's Introduction to SPC, Breyfogle's Implementing Six Sigma, Pyzdek's Six Sigma Handbook, AIAG's SPC (2nd), and ASTM's 7th edtion of Presentation of Data and Control Chart Analysis. When you select data and then run a control chart using the QI Macros pull down menu, the QI Macros will use ALL of your data points to calculate the average. The average field is then used to calculate the 1 and 2 sigma lines and the upper and lower control limits. If you have 14 points it will use 14 points, if you have 26 points it will use 26 points, if you have 50 points it will use 50 points, etc. To view which points are used to calculate your average, simply click on the data sheet created by the QI Macros (e.g. pdata1) and then click on the first cell under the cell labeled "Average". You can determine the data used in the average formula in one of two ways. 1. View the formula in Excel's formula bar. This will show the range of data that is used to calculate the average. In the following example, we clicked on cell H2 and noted that the average is calculated using cells B2 to B26 and C2 to C26.
2. Depending on the chart, these formulas can be hard to read. Another way to determine the cells used to calculate the average is to use the F2 function key. The function keys are located at the top of your keyboard. When you click on a cell and then the F2 function key, Excel will highlight the cells used in the formula for that cell. In the example below, Excel draws a border line around cells B2 to B26 and C2 to C26.
Show Process Change on a Control ChartThe Show Process Change function in the QI Macros will allow you to calculate two or more sets of control limits on a control chart. To run the function, you must first select the data point where you want to start the new set of limits. To select the data point on your chart, click on the point once to select the whole line and then click on the point a second time to select just the point. Excel will highlight the point you selected and the point before it. Then, click on the QI Macros pull down menu and select Show Process Change on Control Chart.
The QI Macros will create a separate set of limits. The first set uses all of the data points to the left of the selected point and the second set uses the selected point and all points to the right. Next click on the pull down menu and select Analyze Stability to re-run the stability analysis with the new limits. You can do this more than one time on the same chart.
Recalculate Control Chart Limits to Include New Data Added to an Existing ChartWhen you run a chart using a macro from the pull down menu and then add data to the existing chart, the control limits DO NOT recalculate. If you want the control limits to recalculate every time you add data, there are two options.
Change Control Chart Limits Manually After Running a ChartOften, after stabilizing a process, you will want to "lock" the control limits. Then you'll be able to easily compare current performance with past performance. Doing this with the QI Macros is easy. Let's say you've run an XmR chart using our manufacturing data (c:\qimacros\testdata\XmRchart.xls):
Since there are 30 data points, all 30 were used to calculate the control limits. But what if you wanted to only use the first 19 points as the data points that represent your process performance? Switch over to the XmRdata worksheet and select the formula in cell F2:
Either change the formula in the formula bar or click on the F2 function key and Excel will highlight the data that the formulas use to calculate the average (B2:C30):
Now simply click on the circular tag at the bottom right-hand corner of cell B30 and drag your mouse up until you're only including B2:B20:
This changes the average formulas to include just data from batch 1 to batch 19. Next, click ESC to get back to a normal view. Notice that the average calculated in cell F2 is different than the average calculated in the rest of the column. This is because you have only updated the formula in cell F2. You must copy and paste the new formula from cell F2 into the rest of the cells in column F. Use Excels Edit/Copy and Edit/Paste functions to do this.
Now click on the XmR chart sheet and notice how your limits have changed:
Since we're working with an XmR chart which has two charts the X and the moving Range, you will also have to do this adjustment for the range chart. That's all there is to it. For more information on control charts, consider our Control Chart Whitepaper. Management Dashboards and Scorecards
In Lesson # 10 we will cover Histograms. View previous lesson .......................View next lesson
© 2007 KnowWare International Inc. (888) 468-1537 |
It contains over 70 fill in the blank templates such as the Ishikawa diagram, QFD, DOE, FMEA, PPAP, and Gage R&R for MSA. Performs ANOVA, t-test, F-test, and regression analysis.
|
|||||||||||||||||
