Control Chart Limits

Jay Arthur
888-468-1537
303-756-9144
KnowWare International, Inc.
DBA LifeStar
2253 S. Oneida
Ste 3D
Denver, CO 80224

We work with companies
that want to fire up their profits using
Lean Six Sigma

Copyright © 2011

In this Issue

• Control Limit Calculations
• How Control Limits are Calculated Using the QI Macros
• Show Process Change on Control Chart Function
• Recalculate Control Limits to Include New Data Added to an Existing Chart
• Change Control Limits Manually After You Run a Chart

Control Chart Limit Calculations

Generally, 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 Macros

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 Chart

The Show Process Change function in the QI Macros will allow you to calculate two different 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 two sets 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.

Recalculate Control Chart Limits to Include New Data Added to an Existing Chart

When 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.

1. You might consider using one of the fill-in-blanks control chart templates. These are set up to take 50 data points and the control limits will recalculate every time you add new data. Click on Fill in the Blanks Templates on the pull down menu to access these.
2. Use the Show Process Change function to recalculate the control limits. To do this, select the first point (farthest to the left) on the chart and then select Show Process Change on Control Chart. Since there are no points to the left of the first point, no first set of limits will be calculated. The second set will include the point selected and all points to the right - including your new data.

Change Control Chart Limits Manually After Running a Chart

Often, 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 B2:B19 data.

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.

To purchase the QI Macros

If you already own the QI Macros, you can purchase an upgrade. You must purchase one upgrade for each license you own.

Try QI Macros
FREE
For 30-Days

Or Buy It Now!