Automatic Control Charts

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

Do you routinely pull information from corporate databases and then use the QI Macros tools to draw control charts? If so, there might be a better way using the control chart templates.

Instead of doing charts every month or week, here's how to link your data to the QI Macros control chart templates. Every control chart template has a place for up to 50 data points (or more as required) which create the chart.

Here's an example of the XbarR template. Using the data in the yellow shaded input area, this template provides the average and range control charts, histogram, normal probability plot, and scatter plot.

To automate the production of your charts, all you need to do is link your monthly data spreadsheet to one or more of these template input areas.

Adding a Control Chart Template To Your Workbook

The first step is to add a template to the same workbook that your data is in. Click on QI Macros, Fill-in-the-blanks templates, SPC, and the appropriate template. In this case, the data is variable (money), so we choose the XmR Four Pack.

This will bring up the XmR Chart template:

Now, just move or copy it into the corporate database workbook using Edit-Move or Copy Sheet to move the template to the end of your workbook:

Creating Useful Links

Let's suppose that every month you get a data dump that looks like this:

Using Excel, you can select the labels and data for your chart and use Paste Special to paste a link into another sheet (in this case the template input area).

This may work for you, however, Excel treats blanks as zeros which can be a problem. Also, Excel won't allow you to paste link and transpose data. If you have zeros or need to Paste Link and Transpose at the same time, we have created QI Macros tools to do both. Just select your data and click on Data Transformation-Paste Link (or paste link transpose if your data is horizontal).

Tip: Select more rows than currently exist to cover you for the next several reporting periods.

The QI Macros will create a new sheet (Sheet 1) with the formulas and links you need to automate your control charts:

Adding Links to Your Control Chart Template

Now all you have to do is add the "paste links" created earlier to this template. To do so, simply:

1. Click on Sheet 1 (in this example)
2. Edit-Copy the cells with the paste-link formulas in them.
3. Switch to the control chart template.
4. Click on cell A1
5. Use Edit-Paste Special and click on the Formulas button to paste the link formulas into the worksheet.
   
6. Similarly, use Edit-Paste Special again and select Formats to paste the date and number formats into these cells.
7. If you have less than 50 data points, click on the autofilter arrow in cell B1 and select show non-blanks.
   

This will collapse the chart to show just the available data points.

If you want to, you can click on the chart and run stability analysis from the QI Macros Chart menu.

Repeat this process for every chart you produce from this data dump.

Here's the beauty of this process: next month, just copy and paste the data from the new data dump into the data sheet in this workbook. You'll have new charts!

Automating the Autofilter

Now, let's say that you have fewer than 50 data points and you're adding one a month. You don't want to go back into every control chart and Autofilter for non-blanks. Here's how to do it with hidden code.

1. Right click on the template (e.g., Charges) and select "View Code":
   
   This will reveal the hidden code to autofilter this sheet:
   
2. The apostrophes in front of every line change the lines to comments. Simply delete the apostrophes (in this order) before End Sub, Selection.Autofilter, Range("B1), and Private Sub to activate the code. When you're done, it should look like this:
   
   
3. The next time you click onto this sheet, the code will automatically autofilter the report to include the new data.

Pretty slick huh?

Adding Data to the Charts

If you have more than 50 data points, you can copy the last row of control chart formulas and paste as many rows as you need. Then just change the charts to include the additional data points.

1. Select and copy the last row of control chart formulas:
   
   
2. Paste as many rows as you need. (Hint: if you paste linked more than 50 data points, the formulas should be in cells A52:B52 and so on.) If not, you'll need to redo the paste links of the formulas.
   
3. Click on the X chart and QI Macros Chart Menu-Add Rows to Control Chart.
   
4. Indicate the number of rows you just added.
   
5. If you are adding more than 50 rows, you will need to change the number of rows for the process change to something larger than the total number of rows:
   
6. Do the same for the R chart.

Rolling Your Monthly Reports

If you want to "roll" say a 24 month period, but your database gives you every month since time began, just copy and paste the formulas up a row every month:

1. First click on the autofilter arrow in B1 and select "All" to show all of the formulas.
2. Select A3:B51 (or whatever is the last row)
3. Choose Edit-Copy
4. Then select A2 and choose Edit-Paste Special-Formulas.
5. The chart will roll up one period.

In Summary

It's possible to create a dashboard of measures that are driven off your monthly database downloads. Just use this month's download, the QI Macros Paste-link functions, and the QI Macros fill-in-the-blanks control chart templates to build your dashboard.

Once it's built, you can paste new data downloads into it indefinitely.

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.

Dashboards and Scorecards

Download
QI Macros
30-Day Trial Now!

Or Buy It Now!