Control limit calculations begin with the Center Line (the average or median of the data.)
Next calculate sigma. The formula for sigma depends on the type of data.
From the center line, lines are drawn at ± 1 sigma, ± 2 sigma and ± 3 sigma.

+ 3 sigma = Upper Control Limit (UCL)
- 3 sigma = Lower Control Limit (LCL)

How are Control Limits Calculated?

The simple answer: the average of your data ± sigma.

The hard answer: The formula for sigma depends on the type of data you have. Is it continuous or discrete? What is the sample size? Is the sample size constant?

In short, each type of data has its own distinct formula for control limits and, therefore, is a different type of control chart. There are seven main types of control charts (c, p, u, np, individual moving range XmR, XbarR and XandS.) Plus there are many more variations for special circumstances. As you might guess, this can get ugly. Here are some examples.

p chart formula

Individual Moving Range chart formula

X bar R chart formula

You Can Try and Calculate Control Limits Yourself, But ...

It will suck up a bunch of your time.
You'll probably make mistakes and aggravate customers.
And you will find your homegrown template hard to maintain.

Based on calls to our tech support line, most people who try to perform manual calculations or build their own Excel formulas end up with incorrect results.

Instead, Use Proven Software like QI Macros Add-in for Excel

QI Macros is an easy to use add-in for Excel that installs a new tab on Excel's toolbar.

QI Macros calculations are tested and accurate and it will highlight unstable points and trends in red.

Even better, QI Macros control chart wizard contains code that will select the right chart and formulas for you!

How Many Data Points are Used to Calculate Control Chart Limits?

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.

When you select data and then run a control chart using QI Macros, QI Macros will use ALL of the data points selected to calculate the center line (ie. average). If you have 14 points it will use 14 points, if you have 26 points it will use 26 points, etc.

The center line is then used to calculate the 1 and 2 sigma lines and the upper control limit and lower control limit.

To check which points are used to calculate your center line, simply move the chart to reveal the calculations behind it. Click on the first cell under the cell labeled "Average". You can determine the data range used by viewing the formula in Excel's formula bar.

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.

QI Macros Makes it Easy to Update Control Limit Calculations

Once you create a control chart using QI Macros, you can easily update the control limits using the QI Macros Chart Tools menu. To access the menu, you must be on a chart or on a chart embedded in a worksheet.

Here's what you can do with the click of a button:

Show Process Change (i.e. stair step control limits)
Fix Control Limits
Ghost a Point - leave data point on a chart but remove it from control limit calculations
Delete a Point - remove a point from the chart and from control limit calculations
Recalculate UCL/LCL - recalculate control limits after adding new data

It's easy to show process changes on a control chart created by QI Macros. The process is different depending on how you create your control chart:

New Charts Created with a Macro

To calculate two or more sets of control limits on new charts simply leave a blank row between the data points where you want the limits to change:

Select the data including the blank row(s) and run the chart. You should get a chart with two or more sets of control limits:

Existing Charts Created with a Macro

Go to the chart and click on the first data point where the process change occurred.

Tip: The first time you click on a point, Excel will select the whole line. Click on the point a second time to select just the point. If you have done this correctly, Excel will highlight your point.

Click on QI Macros chart menu and select Show Process Change:

The macros will calculate a new set of control limits starting at the data point you selected. Note: you can do this more than once on a chart. The example below shows three separate sets of control limits:

Don't forget to rerun stability analysis using the new control limits. Click on the chart sheet. Use QI Macros menu to select "Analyze Stability With Control Chart Rules." QI Macros will rerun stability analysis using the new control limits.
Note: Performing these steps on an X chart will not update the R chart. You will need to update the R chart separately.

Want to Create an Interrupted Time Series Using QI Macros? See Instructions Below:

You can create an Interrupted Time Series chart by simply performing a Show Process Change at the end and beginning of your two different series!

To do so, place a blank row in between your two data sets, and create your Control Chart:

There are also options to easily re-run stability analysis after changing data or control limit calculations.

Remove Point from Control Chart Calculations
(Ghost Point)

While it is not usually recommended, many customers have asked for the ability to leave a point on a control chart BUT remove the point from the control limit calculations. Using the chart pull down menu, QI Macros now give you this capability.

To remove a point from the calculations but leave it on a chart:

Click on the point to be removed.

Select the QI Macros Chart menu and select Ghost Point.

The point marker will change to designate that the point has been "ghosted."

Next re-run stability analysis with the new control limits.

If you want to check the control limit calculations, go to the data sheet where the formulas are created and click on one of the average cells. Look in the formula box to view the formula. You will notice that the row that the ghosted point is in has been removed from the calculations. In the example below, the point in row 19 has been removed

You may also want to add text to the point to further highlight that it has been removed from the calculations.

Delete a Point from a Control Chart

Remove Point from a Chart and from Control Limit Calculations

It's easy to delete a point from a control chart created using QI Macros. The process is different depending on how you create your control chart:

• Control Charts Created with a Macro

• Control Charts Created Using Control Chart Templates

• Excel 2007 Workaround for Deleting a Point on an Embedded Chart

Control Charts Created with a Macro

To delete a point from a control chart:

Click on the chart.
Click on the point you want to delete.

Click on the QI Macros Chart menu (to the far right) to open the control chart tools menu (on the far left). Select "Delete Point".

Rerun stability analysis without the deleted point by selecting the QI Macros Chart menu and selecting "Analyze Stability."

Recalculate UCL and LCL on Control Charts

Recalculate Control Limits on Charts Created with a Macro

After adding new data to a control chart, click on the chart and then click on the QI Macros chart menu and select Recalculate UCL and LCL

The macros will re-calculate the control limits using all of the data points.

Don't forget to rerun stability analysis using the new control limits. Click on the chart sheet. Use QI Macros menu to select "Analyze Stability With Control Chart Rules." QI Macros will rerun stability analysis using the new control limits.

Quick Navigation;