|
© 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
Control Charts Created with a Macro - Watch
Video
When you run a macro you end up with 3 spreadsheets: your original datasheet,
the chart and an intermediate spreadsheet where all of the calculations
are performed. To add data to an existing chart:
- Click on the intermediate data sheet (e.g., pdata1) and add the new
data and labels in the rows under your old data.
- If necessary, use Edit/Copy and Edit/Paste to copy any formulas down
to the new rows.
- Click on the chart sheet. Then click on the QI Macros pull down menu
and select "Add Data to Control Charts"
This menu is sometimes hard to find in Excel 2007s ribbon. To access
the menu, click on the chart then on Chart Tools (just above QI Macros).
- Input the number of rows of data you just added to the intermediate
spreadsheet and click OK. This function will change the "source data
range" of the chart and will update the chart to display your new data.
- To rerun stability
analysis on the chart. Click on the chart sheet. Use
the QI Macros pull down menu to select "Analyze Stability".
The QI Macros will rerun stability analysis on your old and new data.
Control Charts Created with a Template - Watch
Video
Control Chart templates are
set up to take 50 data points. To add data to a template:
- Make sure the menu in cell B1 is set to ALL so that all input rows
are visible.
- Simply input data into the next blank row.
- Once you have used all 50 rows, you will need to expand the template
to take more rows.
- First use Edit/Copy and Edit/Paste to copy the formulas down into
the new rows.
- Next select the chart and select the "Add Data to Control
Charts" on the QI Macros pull down menu. Input the number of
rows you just added.
- Re-run stability analysis
whenever you have added new data by clicking on the chart and then clicking
on the QI Macros pull down menu. Select Analyze Stability to run stability
analysis on your data. Any unstable point or condition will turn red.
Control Chart Limits
Control limit calculations are based on the average and variability
of your data and your sampling strategy. Generally, you should use at
least 20 to 25 subgroups to calculate the average and the baseline control
limits. Future data points should be evaluated against the baseline. You
should only recalcuate the average and corresponding control limits when
you have a process change. See Lesson 9 for more
discussion about Control Chart Limits.
Control Charts Created with a Macro
When you select data and then run a control
chart using the QI Macros pull down menu, the QI Macros will use ALL
of your data points to calculate the average. If you have 14 points it
will use 14 points, if you have 26 points it will use 26 points, if you
have 50 points it will use 50 points, etc.
If you want to change the range used to calculate the average/control
limits you will need to change the average formula in the intermediate
spreadsheet created by the macro. You can do this in one of two ways.
- Click on the first cell under the column labeled "average"
and change the formula in Excel's formula bar. The formula will tell
you which range of cells is used to calculate the average. Change the
formula to the range you want. Then copy the formula down to the remaining
rows in the spreadsheet.
- Depending on the chart, the average formulas can be hard to understand.
Another way to change the cells used to calculate the average is to
use the F2 function key. The function keys are located at the top of
your keyboard. When you click on a cell and then the F2 function key,
Excel will draw a border around the cells used in the formula for the
selected cell. To change the formula, simply click on the lower outside
corner of the border and drag it over the cells you want to include.
Control Chart Templates
Control chart templates are
set up to calculate the average using all of your data points. Whenever
you add data to a template, the average and the control limits will change.
Show Process Change on a Control
Chart by Creating Stair Step Limits
New Charts Created with a Macro
To calculate two or more sets of control limits on new charts simply
leave a blank row between the data points where you want the limits to
change.
Select the data including the blank row(s) and run the chart. You should
get a chart with two or more sets of control limits.
Existing Charts Created with a Macro
- Watch
Video
- Go to the chart and click on the first data point where the process
change occurred. Note: The first time you click on the point,
Excel will select the whole line. Click on the point a second time to
get just the point. If you have done this correctly, Excel will highlight
your point and the point before it.
- Click on the QI Macros pull down menu and select Show Process Change
on Control Chart.
- The macros will calculate a new set of control limits starting at
the data point you selected. The macros will also update the UCL, Average
and LCL labels to reflect the separate set of control limits. Note:
you can do this more than once on a chart. The example below shows three
separate sets of control limits.
-
Don't forget to rerun stability analysis using the new control
limits. Click on the chart sheet. Use the QI Macros pull down
menu to select "Analyze Stability". The QI Macros will rerun
stability analysis using the new control limits.
- Note: Performing these steps on an X chart will not update
the R chart. You will need to update the R chart separately.
Control Chart Templates - Watch
Video
- Look for the box to the right of the control chart marked Process
Change at Row.
- Input the row in the Excel spreadsheet where the process change occurred.
- The template will calculate a new set of control limits starting at
the data point in the row you entered. On the X
chart templates the process change will be reflected on both the
X and R charts.
- Don't forget to rerun stability analysis
using the new control limits. Click on the chart sheet. Use
the QI Macros pull down menu to select "Analyze Stability".
The QI Macros will rerun stability
analysis using the new control limits. Note: This step needs
to be performed separately for the X and R charts.
Delete a Point from a Control Chart
Control Charts Created with a Macro
To delete a point from a control chart:
- Click on the chart.
- Click on the point you want to delete.
- Click on the QI Macros pull down menu and select Delete Point from
Control Chart.
- Rerun stability analysis without the deleted point.
Control Chart Templates
To delete a point on a control
chart template simply delete that point or row from the input area.
Add Target or Goal Lines to Control
Charts
Remember control limits are calculated
from your data. However, you can also show a target or goal line on your
control chart in one of two ways:
- Use Excel's drawing tools to simply draw a target line on your existing
control chart. Use Excels drawing tools to add a text box to label the
line. Note: If you add data to the chart you may need to re-synch
up the line.
- Add target data to the chart.
- Create a new column in your intermediate data sheet and label
it "target" or "goal." Input your target value
in every row that you have data. For example, if you have 25 data
points on your control chart and your target value is .175, input
".175" in the first 25 rows of your column titled Target.
- Click and drag over the title and target data and select Edit/Copy.
Then go to the chart and select Edit/Paste to add the target line
to the chart.
If your users are not very familiar with Excel and need some extra help
using these more advanced features consider the QI
Macros Training CD, our Control
Chart Whitepaper, or an on-site QI Macros workshop. Call us at 303
756 9144 to check on Jay's availability in your area.
Are you spending too much time updating monthly
charts and graphs? Is your analysis out of date by the time
it is completed? We have created customized solutions for many of our
customers to save them time and headaches. See http://www.qimacros.com/excel-apps.html.
In Lesson
#6 we will cover ANOVA and other Statistical Analysis.
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
|
