Tips on Formatting Your Data Before You Run a Chart

home · products · services · search · view cart · contact  
 
  NAVIGATION        
 

Jay Arthur
888-468-1537
303-756-9144
KnowWare International, Inc.
DBA LifeStar

2253 S. Oneida
Ste 3D
Denver, CO 80224

We help people think!

Copyright © 2009



 

In this Issue

Formatting and Selecting Your Data Correctly

We have tried to fool proof the QI Macros, but we have to make certain assumptions about the data you select before running a chart. One assumption is that you have no more than one row of headings and one column of labels. If you have two rows of headings or two columns of labels BE CAREFUL. Selecting the extra columns or rows will create problems with your calculations and with your chart.

The example below has two rows of headings. Row 1 "Smoothness" and Row 2 : date, time, Obs 1, etc. It also has two labels. Column A for date and column B for time.

data with two heading rows and two columns of labels

The Problem with Selecting Two Rows

If you select the two heading rows like this:

data with two rows of headings

You will get a chart that looks like this:

XbarR chart

Notice that the first point graphed on the chart is zero. This is because the macro interprets the second heading row as data and gives it a value of zero. This also makes the average and sigma line calculations incorrect.

The Problem with Selecting Two Columns

If you select two columns of labels like this:

data with two columns of  labels

Your chart may look okay, but if you look in the chart data sheet where the calculations are performed, you will notice that column B is included in the calculations. In the example below, cell H3 which calculates the average of the data points is including the time label in column B as part of its calculations. A value of 7 is being added to the values of 5.1, 9.1 etc. to calculate the average in column H.

data formulas

Fool proof your charts by formatting your data with only one descriptive row and column.

Case Study Smoothness of Gel Caps from Quality Magazine Brain Teaser

The following case study uses Dr. Sophronia Ward's Brain Teaser column in the December 2005 issue of Quality Magazine.

To read the complete December 2005 Brain Teaser in Quality Magazine go to "Perfectly Smooth"

Background

After a recent improvement in the surface smoothness of gel capsules, a process engineer received complaints from a customer that the capsules were sticking to the chutes on the production line. After ruling out the machine as the cause, the team looked into the surface smoothness of the gel caps.

You can download the data for this example at: http://www.qimacros.com/free-excel-tips/gelcapdata.xls.

Case Study Question 1: Is there evidence in the data that the recent efforts to improve the gel cap smoothness did result in a change to the surface smoothness?

To determine this, we will want to run a control chart on data before and after the process change. The data in Dr. Ward's example looks like this:

Reformat the Data Before You Run the Chart

The data table provided by Dr Ward shows the "before" and "after" data side by side. In order, to run a control chart using the QI Macros we need to move the "after" data below the "before" data. Select the data and then select Edit/Cut, next click on the cell below the before data and select Edit/Paste.

Using a Blank Row to Show Before and After a Process Change

Finally, insert a blank row between the Oct 5 data and the Oct 12 data to take advantage of the QI Macros ability to calculate different sets of control limits for the before and after data. Your data should look like this.

blank row to show process change

Selecting Your Data for the XbarR Chart

Since we have 5 samples or observations for each date and time we will select the XbarR chart. The XbarR chart will calculate an average for the 5 points for each date and time and graph these averages. The XbarR chart is sometimes referred to as the average and range chart. Using the rules discussed above we will only select one descriptive row and column when we select the data to run a chart. Our selection looks like this:

XbarR chart data

The X chart looks like this:

XbarR chart

The Range chart looks like this.

range chart

 

Response to Question 1: The before and after charts show a very unstable process right after the process change and then a definite decrease in surface smoothness. The range chart shows that the variation in surface smoothness has decreased as well.

Case Study Question 2: Is there evidence in the data to indicate that the new gel caps are too smooth?

Response to Question 2: The smoothness has definitely changed, but it would require more analysis to determine if they are too smooth.

Case Study Question 3: How can the process engineer determine the optimal surface smoothness?

Response to Question 3: They would need to run a designed experiment.

Here's the Point

The QI Macros are smart but they are not fool proof. Remember this rule when you select your data. Only select one row and one column of headings or labels.

If you are looking for manufacturing case studies, a great resource is Dr. Sophronia Ward's Brain Teaser column in Quality Magazine. You can view back issues on the Quality Magazine website.

If you need help analyzing your data, we offer data analysis services via email. Just send us your data and a brief explanation of your process and suspected problem. We will send you a quote to analyze your data and create an actionable improvement project to focus on the 4% of your process that causes 50% of the lost profit. Email your data to knowwareman@qimacros.com.

To sign up for this series tell your friends to send an email to qimacros@aweber.com

To purchase the QI Macros

QI Macros - Download with backup CD & User Guide (#230) $139 + S&H
QI Macros - Download Only - No Shipping or Customs Duties (#W230) $139


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

QI Macros Upgrade - Download with backup CD and user guide (#232) $69 + S&H
QI Macros Upgrade - Download Only - No Shipping or Customs Duties (#W232) $69


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.
 

Lean Six Sigma Consulting

Download
FREE
30-Day Trial



QI Macros SPC Software for Excel

Or Buy It Now
for only $139!
Unconditional
90-Day
Money-Back
Guarantee

Add QI Macros Starter Kit - SPC Software plus training CD and tools book (#275) $197 plus S&H
Add SPC Simplified System - SPC Simplified Book and DVD, Plus SPC Software, Training Cd and Tools Example Book (#285) $297 plus S&H


 
home | products | services | search | sitemap | view cart | contact
QI Macros | Excel SPC Software | SPC Software Free Trial
Control Charts | c Chart | np Chart | p Chart | u Chart
ImR Chart | XmR Chart | XMedianR Chart | XbarR Chart | XbarS Chart | EWMA Chart
Anom Chart | Cusum Chart | Levey Jennings Chart | Moving Average Chart
Histograms | Pareto Chart | Scatter Plot | Run Chart
Box Whisker Plot | Dot Plot | Multivari Chart

Lean Six Sigma Training | Lean Six Sigma Training Denver
Lean Six Sigma Consulting | Lean Six Sigma Consulting Denver
Lean Six Sigma Training Systems | Do-It-Yourself Lean Six Sigma Training