Applications for the QI Macros Templates

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

Did you know that the QI Macros has a fill-in-the-blanks template for most of its charts including the pareto chart, histogram and every control chart?

To access these templates click on the QI Macros pull down menu and then click on Fill-in-the Blanks-Templates then SPC Charts.

Following is a case study which provides some ideas on how to use the templates and covers the following functions:

• Converting data to x/defects per 1000 and running an XmR chart
• Copying or moving whole sheets to other workbooks
• Using paste link to update a chart when you update your original datasheet
• Using Excel's autofilter function on the template charts

Case Study - Measuring Reduction in Mortality Rates

One health care group initiated a Rapid Response Team (RRT) to respond to the 100,000 lives challenge (save an extra 100,000 lives in the next year).

Studies by the IHI.org found that 48% of deaths occurred outside of the ICU for non-comfort care. The goal is to prevent cardiac arrest by detecting the most common warning signs (acute changes in heart rate, systolic BP, respiration rate, saturation, O2, or conscious state) and taking immediate action.

For the team to measure its effectiveness, it needs to know:

1. How many "codes" (i.e., cardiac arrests) occur per 1000 discharges.
2. How many codes occur outside of the ICU
3. Use of the rapid response team

Using the templates in the QI Macros, it was easy to set up these measures.

1. Calculating Codes/1000 Discharges: I opened an Excel spreadsheet and typed in the key inputs for each of the three measures. I used a formula in D3 to calculate the codes/1000 discharges. The formulas is =SUM(B3/C3). I then copied the formula down into the other rows below.
   
   
2. XmR Chart Template: Using the QI Macros, I went into the Fill in the Blank Templates then SPC Charts then XmR Four Pak. This template has all of the working information I need to graph the codes/1000.
   
   
3. Moving or Copying Sheets to Another Workbook: Using Excel, I chose Edit-Move or Copy Sheet, to move the template to the end of my measures workbook. Just click on the "To book:" arrow to select the destination data sheet. Click OK and Excel will move the XmR template to the selected workbook.
   
   
4. Copying Cells: Then, I went back to my data sheet and selected the dates in column A and chose Edit-Copy.
   
5. Using Paste Link: Then, I switched to the XmR chart template and in cell A1, I chose Edit-Paste Special and clicked on the Paste Link button to copy a "link" from the data sheet to the template. This way, I can update the data on the data sheet, and the XmR Chart template will update automatically.
   
   
6. Then I did the same thing for the codes per 1000 discharges column. Once you populate the input cells (shaded in yellow), the chart draws itself automatically.
   
   
7. Use control-key to select more than one worksheet:Then, because the second measure is about the same format as the first one, I simply clicked on the codes/1000 data sheet, held down the control-key and clicked on the XmR Chart of codes/1000 to select both sheets.
   
   And again I selected Edit-Move or Copy sheet. This time I checked the "copy" box to make a complete copy of both the data and the chart.
   
8. This makes a complete copy of the data and chart sheets. This made it easy to fill in the new column headings to create the new codes/total chart.
   
   
9. c chart template: For the third measure, I chose the c chart because we're measuring numbers of calls, but there is no denominator for this measure. And I did the same thing as I did for the first one.
   
   
10. Now all the team has to do is drop their numbers into the data sheet and the control charts will draw for them automatically.
11. Using Autofilter in the Templates: If you don't like all of the empty space on the right of the chart, simply click on the arrow in column B1. Select "nonblank" and the chart will redraw itself with just the current data. This is the "Autofilter" function you will find under Excel's Data toolbar. This is a great way to reduce the amount of data you see.
    
    
12. What could be easier than that?

If you love the QI Macros...
Please ask a handful of friends to download the trial version. Here's what to say:
Need easy to learn, easy to use software for Lean Six Sigma and SPC that works right in Microsoft Excel? Test drive the QI Macros!
Find out more at www.qimacros.com/excel-spc-software.html.
Get a 30-day trial at www.qimacros.com/free-spc-software.html.

Going to a meeting of quality professionals?
We can send you as many QI Macros 30-day trial CDs as you need. Just send an email with the address and quantities needed to orders@qimacros.com.

Try QI Macros
FREE
For 30-Days

Or Buy It Now!