|
© 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
There are two different ways to create most charts in the QI Macros.
One is by selecting your data and then running a macro from the pull down
menu. The second is by using the Fill-in-the-Blanks templates.
To create a chart using a macro from the pull down menu:
(Watch Video of
this process)
- Just select the data to graph (using
the mouse to click and drag).
- Then, using the QI Macros pull down menu, select the chart you want
to graph.
The QI Macros will do the math and draw the graph for you. The control
chart macros will run stability analysis
on your data and turn any unstable points or conditions red.
If you don't know which control
chart to select, choose the Control
Chart Wizard. It will analyze your data and choose the right chart
for you:
Control Chart Wizard in Excel 2003 and previous versions:
Control Chart Wizard in Excel 2007:
Creating a Chart with a Fill-in-the-Blanks
Template
You can also create charts with the Fill-in-the-Blanks Templates. These
templates are especially helpful if you have non-quality department personnel
(e.g. at nursing stations or on the shop floor) who will be inputting
data or you don't have enough data (you're starting to collect the data).
To create a chart using a template:
(Watch
Overview Video)
- Click on the QI Macros pull down menu, select Fill-in-the-Blanks Templates,
then the sub menu, then the chart you want to open.
Excel 2003 and previous versions:
Excel 2007:
- The input areas for all of the templates are shaded yellow. Either
input your data directly into the template, cut and paste it from another
Excel spreadsheet or set the template input cells equal to cells in
other spreadsheets.
As you input data, the chart will populate to the right. The
X chart templates also display a histogram,
probability plot and scatter
plot.
Running stability analysis on a chart created
by a template. To run stability
analysis on a chart created using a control chart template: click
on the chart (dark boxes will appear at the corners), click on the QI
Macros pull down menu, select Analyze Stability.
Choosing which points to plot.
Each template defaults to 50 data points. If you have fewer than 50
points and only want to show the points with data, click on the arrow
in cell B1. This will bring up a pull down menu. Select "non-blanks"
to plot only the points with data.
If your users need some extra help running the macros, consider the
QI Macros Training CD.
It includes step by step instructions for loading and using the QI Macros.
You'll see exactly what data to enter and what commands to use to run
each QI Macros chart and template.
Data Selection Tips
If the labels and data are not adjacent, you can use the control key
function in Excel (select the first set of data, press down on the control
key and then select the second range of data.)
Don't select the whole column or row, just the data and associated
labels you want to graph.
If you have two rows of headings or two columns of labels BE CAREFUL.
Selecting the extra columns or rows may 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.
Fool proof your charts by formatting your data with only one descriptive
row and column.
Data Input and Format Tips
When you select the data you want to graph, you can select the associated
labels as well (e.g. Jan, Feb, Mar, etc.) The QI Macros will usually
use the labels to create part of your chart (e.g. title, axis name,
legend). Make sure you follow these rules when inputting your data.
- Labels should be formatted as text.
If your labels are numbers (e.g. 1,2,3) you may need to make them text
so that Excel doesn't treat them as part of your data. To do this, you
will need to put text in front of them. Some examples are :Sample1,
S1, Lot1, L1. If you just want the 1 to show then you will need to put
an apostrophe ' in front of each number to change it from data to text
(e.g. '1, '2, '3 etc.) Note: just using the format function in
Excel does NOT work.
- Data should be formatted as numbers.
Your data must be numeric AND formatted as a number for the macros to
perform the necessary calculations. If you have "data" that
is left justified or looks like 001, 002, 003 then it is formatted as
text and the macro will not run.
Tip: Data exported from Microsoft Access is often in text format.
To change it to numeric: 1) import the data to Excel, 2) put the number
1 in a blank cell, 3) Edit-Copy the cell with the 1 in it, 4) select
the imported text data, and 5) choose Edit-Paste Special/Multiply to
multiply every imported cell by 1 which will convert them to numeric
data.
- Select the right number of columns.
Each chart requires a certain number of columns of data to run properly.
They are:
- 1 column: Pareto, Pie,
Run, C chart, np
chart, XmR chart
- 1 or more columns: Line, Bar, Histogram
- 2 columns: Scatter,
u chart, p
chart
- 2 or more columns: Box
& Whisker, Multivari,
XbarR and XbarS
- Beware of "hidden" rows or columns.
If you select columns A:F, but B and C are hidden, the QI Macros will
use all five columns including the hidden ones. To select columns that
are not adjacent to each other use the control key.
- The QI Macros and the statistical tools work
best when data is organized in columns, not
rows. So, for an XbarR chart, you might have Sample1, Sample2,
... Sample5 across the top, and then lot numbers or dates down the left-hand
side. The macros will work if your data is laid out horizontally in
rows instead of in columns, but vertical columns is the preferred method.
- Decimal places. To get the correct
number of decimal places, format your data as Numeric (1-6 decimal places)
vs General (which has no precision).
Using the QI Macros Pivot Table
Wizard to Summarize Your Data
If you have raw transaction data, you may need to summarize it before
you can run a chart like the Pareto chart. Use the Cross Tab Pivot
Table Wizard to summarize your data before you run your charts. Watch
Video.
If you need help creating a pivot table that can be easily updated or
if you are spending too much time updating monthly charts and graphs then
you might consider our Creating
Dashboards and Scorecards Ebook or our custom Excel programming services.
We have created customized solutions for many of our customers to save
them time and headaches. For a quote, send your data and a description
of your requirements to knowwareman@mindspring.com.
For examples of applications we've built, see http://www.qimacros.com/excel-apps.html.
Sample Test Data
The QI Macros for Excel installs test data on your PC in C:QIMacros:Testdata.
Use this data to practice with the charts and to determine the best way
to format the data before you run a macro.
Need extra help understanding when to use each
chart and how to analyze the results? Consider one of the following:
- QI
Macros Training CD - Step by step instructions for loading and using
the QI Macros. You'll see exactly what data to enter and what commands
to use to run each QI Macros chart and template.
- Six Sigma Tools Example
Book - detailed explanations and examples of each chart
- SPC Simplfied Book
- provides detailed explanations of control charts and histograms, the
formulas and how to interpret the results.
- SPC Simplified 1
hour Training Video - Covers stability and capability analysis with
a focus on the charts and what they tell you not the statistics involved.
In Lesson
#3 we will highlight the other tools and templates in the QI Macros.
View previous
lesson .....View
next lesson
To sign up
for this series tell your friends to send an email to qimacros@aweber.com
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
The QI Macros for Excel $139,
is an inexpensive easy to use set of Excel add-ins for statistical process control
and Lean Six Sigma. It draws line, pie, bar, pareto, box whisker, histogram (Cp,
Cpk), scatter and control charts (with stability analysis).
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
|
