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.
The Problem with Selecting Two Rows
If you select the two heading rows like this:
You will get a chart that looks like this:
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:
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.
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.
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:
The X chart looks like this:
The Range chart looks like this.
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@mindspring.com.
To sign up
for this series tell your friends to send an email to qimacros@aweber.com
To purchase the QI Macros go to https://www.qimacros.com/orderform.html.
If you already own the QI Macros, you can purchase an upgrade at https://www.qimacros.com/orderup.html
. You must purchase one upgrade for each license you own.
QI Macros Excel SPC Software for Histograms and Control Charts $139 Plus S&H
