SPC Case Study - Analyzing Different Machines

© 2007 KnowWare International, Inc. DBA LifeStar
2253 S. Oneida St., Ste 3D
Denver, CO 80224

Jay Arthur
888-468-1537
303-756-9144
Email Us

We help people think!

In this Issue

• How to convert HTML data from a website to an Excel Workbook
• Using the Box and Whisker Chart to analyze varying inputs
• Using XbarR stair step limits to analyze varying inputs
• Using histograms to analyze varying inputs

Case Study - Machine Screws

On the governments statistic website, I found a case study about analyzing machines for replacement. The machines produce screws.
http://www.itl.nist.gov/div898/handbook/ppc/section5/ppc521.htm

The essence of the case study is that:

• A machine shop has three automatic screw machines that produce various parts.
• The shop has enough capital to replace one of the machines.
• The quality control department has been asked to conduct a study and make a recommendation as to which machine should be replaced.
• The production specifications for the machines are 0.125+/-0.003 (LSL=0.122, USL=0.128)

The first task is to get the data off the webpage and into Excel.

Converting HTML Data into an Excel Workbook

Using your mouse, all you have to do is select the data in the webpage:

Then use EDIT-COPY to copy it to a Windows clipboard. Next, open Excel and try pasting it into a worksheet. If the data is in an html table, Excel will paste it easily. In this case, however, the data isn't. It's just numbers with spaces between them. So what do you do?

I opened up WordPad (or you could use Word) and pasted the data into a blank page. Then I saved it as a text file (filename.txt):

Excel can take this format and figure out how to import the columns. So I started Excel and opened the text file which starts the text import wizard which will figure out where each column of data belongs:

When finished, Excel gave me the following:

As you can see, there are 10 samples, but they're in a column. To get them into a format that will be usable for graphing with the QI Macros, I need to get the data oriented into groups of 10. To do this, I selected the diameter data and used CTRL-SHIFT-G to start the gentable macro which prompts me for the sample size. It will convert any size array to a different sized array based on sample size:

The result is 10 columns of samples by machine/day/time:

Next, I want labels for each of these rows that correspond to the machine/day/time. So I can use a formula to combine columns A&B&C (D is the sample number):

Then I can use the QI Macros data transformation tools (or gentable macro in older versions) to sort these and give me a set of headings which I can insert in front of the data:

If you had trouble with any of these steps you can download the Excel spreadsheet we created at machinescrewdata.xls. Now we're ready to analyze the data.

Using the Box and Whisker Chart To Analyze The Data

Just select all of the data (A1:K19) and select Box and Whisker from the QI Macros menu:

We could also use the data transformation tools (or gentable macro) to combine all of the data into three machines and draw a box and whisker of that data:

Just by looking at these charts we can see that:

1. Machine 1's output fits squarely between our upper and lower specification limits (LSL=0.122, USL=0.128).
2. Machine 2's output has some below the LSL (0.122). The variation is small, so maybe all we need to do is adjust the machine to move the results up to the target of 0.125.
3. Machine 3 has much more variation and output below the LSL.

Based on just this analysis, I'd recommend replacing machine number 3.

Using Stair Step Control Charts To Analyze The Data

Another way to approach this would be to use the XbarR chart. If we insert blank rows between the three machines, we can analyze the data using stairstep limits:

Again You can see from both the average (X) and the range charts that the variation for machine # 3 is much wider. Again, I'd recommend replacing machine #3.

Using Histograms To Analyze The Data

Another way to do this is to select the data for each machine, run a histogram for each machine, then compare:

Machine 1 has a Cp of .8879 and a Cpk of .8793 (1.0 is considered to be capable). These capability measures tell us that machine 1 has enough variation from part to part that it could produce non-conforming parts.

Machine 2 has a Cp of 1.126 (capable) and a Cpk of 0.36 (off center). Based on these measures, Machine 2 has the least variation; it just needs to be adjusted to center the output on the target (0.125).

Machine 3 has a Cp of .5919 (not capable) and a Cpk of .3989 (not centered). Again, based on Cp and Cpk, I'd recommend replacing machine number 3.

Recommendations

It's so easy to draw charts with the QI Macros that it makes sense to use several different approaches and see what they tell you. In this case, we used the box and whisker, the XbarR using the stairstep capabilities of the macro and three histograms to verify and validate our decision. I don't know about you, but I find that managers often challenge one graph, but when you give them three ways to see the same thing, they're often convinced.

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

© 2007 KnowWare International Inc. (888) 468-1537
knowwareman@qimacros.com
Home | Lean Six Sigma | QI Macros | KnowWare | Site Map

Or Buy It Now!
Unconditional
90-Day
Money-Back
Guarantee

It contains over 70 fill in the blank templates such as the Ishikawa diagram, QFD, DOE, FMEA, PPAP, and Gage R&R for MSA. Performs ANOVA, t-test, F-test, and regression analysis.

Quantity Discounts

Customer Testimonials

Minitab Comparison

QI Macros FAQs