QI MAcros for Excel.pdf

QI Macros Order Form: Qty Six Sigma Resource S&H Price Total

QI Macros for Excel $6 $99.00QI Macros Training CD-ROM $6 $19.95Six Sigma Simplified (128 pgs.) $6 $24.95Six Sigma Instructor Guide (192 pgs.) $9 $39.95Six Sigma System (all of the above) $10 $159.95Six Sigma Simplified (Video,116min) $6 $199.95Six Sigma Simplified (Audio, 4-tapes) $6 $49.95Complete 6σ System (includes A/V) $10 $399.95

Shipping and Handling (U.S.)

Total ❑❑❑❑❑ PC ❑❑❑❑❑ Macintosh

_____ I've enclosed my check, Visa, or MC. I understand that if I amnot completely satisfied, I may return any product for a com-plete refund. 90 Day Money-back guarantee!

To Order, Call, Mail, or FAX your order to :LifeStar, FAX: (888) 468-1536 or (303) 753-9675

Orders: (888) 468-1535 or (303) 757-2039Questions: (888) 468-1537 or (303) 756-9144

2244 S. Olive St., Denver, CO [email protected] www.qimacros.com

Your name: _____________________________Company: _____________________________Mailing Address:____________________________ Apt/Ste. ______P.O. Box: ______________ Email: _______________________City, ST, Zip _______________________, _____ __________Phone: (______) ______-____________Fax: (______) ______-____________ PO#:________________VISA/MC ________ _______ ________ _______ Exp. __/__Signature: __________________________________Prices good until: 6/30/02

© 2001 Jay Arthur 1 QI MacrosQI Macros 36

QI MacrosFor Excel 95, 97, 98, 2000, 2001, & XP

Table of ContentsLine Graphs..................... Ctrl+Shift+L ....................... 4

Pareto Chart .................... Ctrl+Shift+M ...................... 6

Bar Chart ......................... Ctrl+Shift+B ........................ 7

Pie Chart ......................... Ctrl+Shift+O....................... 8

Scatter Chart ................... Ctrl+Shift+S ........................ 9

Histogram........................ Ctrl+Shift+H..................... 10

Multivari/Box&Whisker . Ctrl+Shift+Z/W ................ 12

Selecting Control Charts .............................................. 13

XmR Chart ...................... Ctrl+Shift+R ..................... 14XmR Trend ..................... Ctrl+Shift+T ..................... 14XbarR Chart .................... Ctrl+Shift+X...................... 16XandS Chart .................... Ctrl+Shift+V...................... 16c Chart ............................. Ctrl+Shift+C ...................... 18np Chart ........................... Ctrl+Shift+N...................... 19p Chart ............................. Ctrl+Shift+P ...................... 20u Chart ............................. Ctrl+Shift+U...................... 21 Adding Data To An Existing Control Chart ........... 22

QFD, GageR&R and Other Templates ........................ 24

Ishikawa (Cause-Effect) Diagrams .............................. 27

Flow Charts .................................................................. 28

Tree Diagrams.............................................................. 29

Using QI Macros with Access Databases .................... 31

Trouble Shooting ......................................................... 34

Line Graph

Scatter Chart

Histogram

LSL USL

Control Chart

Flow Chart

To Install Your Macros:1. PC Standalone: Double click or run a:setup.exe

2. Macintosh: Copy all xlm and xlt files in the Excel StartupFolder to the System:Preferences:Excel Startup Folder (5)or Microsoft Office:Office:Startup:Excel folder (Excel 98or 2001). Copy the QIhelp file to wherever Excel resides.

3. Now start Excel. With a workbook or worksheet open, youwill be able to see QI Macros in the Excel Pull-DownMenu Bar (see top line, below, right).

4. Note: If menu does not appear, see Readme file to install.

Quick & Easy Start Up:When you open a workbook, themacros will display a "menu bar" tosimplify running all of the macros.

Then all you have to do is:1. Select the data to be graphed or

choose a template from the selector!(Go to c:\qimacros for test data.)

2. Select the desired graph from the menu bar (opposite) or use theCtrl+Shift+(C) quick keys.

3. Fill in the blanks when the macroprompts you for information.

4. Print and/or save your workbook.

Network Installation

1. PC Network: 1) run a:setup.exe, 2) from Excel's Toolsmenu, select Options and General, 3) enter the AlternativeStartup File Location for XLSTART on your hard drive:c:\QIMacros\XLSTART, 4) click OK.

2. Mac: Network: 1) copy Excel Startup Folder to your disk,2) open Tools/Preferences/General (5.0, 98) enter full pathname for the startup folder. (Office 98 has a select button.)

QI Macros 2 35 QI Macros

5. Hidden rows or columns of data. Users sometimes "hide"a column or row of data in Excel (e.g., Columns show A,B, then F). If you select A-F, you get all the hidden datatoo!

6. Data in the wrong order. Some of these macros requiretwo or more columns of data. The p chart expects 1) aheading, 2) the number of defects, and 3) the sample size. Ifcolumn 2 and 3 are reversed, it won't work properly.

7. Closing Excel takes too long. This is a known Excel bug.Remove the macros from Xlstart and open only whenneeded. See Microsoft Support Article ID: Q75334.

8. To uninstall the Macros: Simply delete all *.xlm and *.xltfiles from Excel's startup folder–XLStart.

9. Email your data and problem to: [email protected] the version number of Excel, Windows, or MacOS.Or call: (888) 468-1537 or (303) 756-9144.

Customizing & EnhancementsYou can adapt and improve these macros and templates as longas you retain the original copyright and don't try to resell them!

• To view a macro, select Unhide under the Excel Windowmenu. Once changed, select Hide to conceal the macro and savethe macro when you close Excel.

• Choose a template from the QI Macros Matrix and DiagramSelector. Then make changes and choose SAVE AS "template".

The QI Macros undergo constant improvement based on yourfeedback. Is there a form, graph, or tool that you think might bepossible using Excel, but you don't have time to figure it out? Isthere something the macros could do to help you make analysismore easy? If so, fax or mail your enhancement requests toLifeStar, 2244 S. Olive, Denver, CO 80224, (888) 468-1536(fax), [email protected]. Call Jay directly at (888) 468-1537 todiscuss your ideas. We also provide custom development ser-vices when needed.


Using The ToolsIntroduction

There are many graphs, forms, and tools used in Six Sigma (forhelp click on the QIWizard at www.qimacros.com):

• Line Graphs • Flow Chart• Pareto Charts • Ishikawa Diagram• Bar Charts • Design of Experiments• Pie Charts • Gage R&R• Control Charts • EMEA/FMEA• Histograms • QFD House of Quality• Scatter Diagrams • Tree Diagram

80% of common problems can be diagnosed with line graphs,pareto charts, and Ishikawa diagrams. Microsoft Excel can beused to create all of these charts, graphs, forms and tools.

Excel Workbooks—Entering your data.

Using an Excel workbook, you can create the labels and datapoints for any chart—line, bar, pie, pareto, histogram, scatter, orcontrol. This gives you a worksheet that looks like this:

Using your mouse, just highlight (i.e., select) the data to begraphed (as shown above), run the appropriate macro, and Excelwill do the math and draw the graph. See c:\qimacros\testdata.

Excel Templates—Using the Tools (see page 24)

Using the QI templates (*.xlt), you can create matrices, flowcharts and all kinds of diagrams using Excel's drawing capability.

Trouble Shooting (see Readme.txt file or

visit www.qimacros.com/techsupport.html)Disclaimer: These macros are not infallible. Given the wrong

data, they will halt. Simply press Halt or Continue.

1. Macros default to two decimal points (e.g., .02): Excelstores most numbers as General format. To get greaterprecision simply select the data and change FORMAT-CELLS to Number and specify the number of decimals.

2. Error: Document with name ......xlm already open. (PC)• Exit Excel. On your hard disk, move the QI macro files—

c:\...\XLSTART\*.XLM to a different folder.

• From the new directory, copy all macro files except forZQImenu.XLM back to c:\...\XLSTART\*.XLM.

• Then copy ZQIMENU.XLM to c:\...\XLSTART\*.XLM.Restart Excel.

Work around: Use Control-Shift<C> to run macros

3. No data (one cell), too much data (entire columns/rows),or the wrong data selected. Are just the essential datacells highlighted? Use the data files included with themacros and the examples on each page to test.

4. Text format data. Are the numbers left aligned? If so, putthe number 1 in a blank cell, select COPY, then select yourdata and choose PASTE-SPECIAL-MULTIPLY.

4. Then select the database you want to use.

5. Then, using the Query Wizard, click on the > and < signs toselect the fields you want in the spreadsheet.

6. Then the Query Wizard will let you filter rows, change the sortorder, and finish the query.

7. Excel will ask you about getting the data and where to put iton the spreadsheet:

8. Excel will then import the data.

9. You can then select Data-Refresh Data to update the data.Excel will go to the Access database and update the spread-sheet.


To Create a Line Graph:1. Highlight the labels and data to be graphed. Click on the

top left cell and drag the mouse across and down to includethe cells on the right.

2. From the QI Macro Menu bar at the top of the page,select Line Graph. Excel will start drawing a line graph.Fill in the graph title, and the X and Y Axis titles.

Short cut: Hold down Ctrl+Shift and press the "L" key (forLine graph).

3. To add text to any part of the graph, just click anywhereon the white space and type. Then use the mouse to click-and-drag the text around. To change titles or labels, justclick and change them. Change other text in the worksheet.

4. To change the scale on any axis, double click on theaxis. Select Scale and enter the new minimum, maximum,and tickmark increments.

5. To change the color on any part of the graph, doubleclick on item to be changed. A patterns window willappear (see next page). Select Font to change text colors,Line to change line colors and patterns, or Marker tochange foreground and background colors.

Line Graph

Using Data from Access1. To link to Access data and get information, you’ll need to

select Tools-Add-ins and check AccessLinks Addin and MSQuery Add-in. If they aren’t visible, choose browse and lookin Library. If they still aren’t visible, you’ll need to copy themin from your installation CD.

2. Then, from anywhere on your worksheet, you can select Data-Get External Data

3. This will start MS Query. Select MS Access Database.

5 QI MacrosQI Macros 32

To change the style of each line on the graph, doubleclick on each line. The window (below) is displayed:

Changing the line style, color, and weight are all performedin this window. When you're done, click OK. The changedgraph is now more easily readable.

To change the scale on the Y axis, double click on theaxis. A patterns window will appear. Select Scale and enterthe new minimum, maximum, and tickmark increments.

6. Select SAVE to retain the chart.

7. To change the style of graph, use the Chart Toolbar(VIEW-TOOLBARS-CHART) or FORMAT MENU—Chart Type.

Click on the desired Graph format. 2-D and 3-D Graphs...include the following. Click on the format and then OK:


Using Macros with Access:Once you have an Access database, you can analyze the databaseby linking Excel and using the QI Macros.

1. The database might look like the following:

2. To use the QI Macros, you have to ensure that the data type ofnumeric fields is number, not text. Use View-Table Design tochange the data type of all numeric fields to ‘number’.

3. Then, select the table, query, form, or report you want to saveand load into Excel. From the Access menu select Tools-OfficeLinks-Analyze It With MS Excel.

4. This will open Excel and create a .XLS file, which you canchart and graph using the QI Macros.

Pareto Chart To Create a Pareto Chart:1. Highlight the labels and data to be graphed (as shown)–

labels in the left-hand column, data in the right-handcolumn. Click on the top left cell and drag the mouse acrossand down to include the cells on the right.

2. From the QI Macro Menu bar at the top of the page,select "Pareto Chart." Shortcut: press Ctrl+Shift+M.Pareto charts are a combination bar chart and line graph.

3. Other Bar: If you have more than nine points, the Paretowill ask if you want to summarize the remaining ones.

4. From the File Menu, select Save to save the graph withyour workbook. (It is difficult to add data to the paretochart; it's best to redraw it if needed.)


Options:1. To add data to an existing graph see pages 22-23.

2. To highlight the cells from different columns (as shown).Click on the top left cell and drag the mouse down toinclude the cells in the first row or column. Then, holddown the Control Key, while clicking and highlighting theadditional rows or columns.

3. You may also use data in horizontal rows. Click on thetop left cell and drag the mouse down and right to includethe cells in the horizontal rows.

4. Numeric data and decimal precision: Excel formats mostnumbers as "General" not "Number". If you do not specifythe format for your data, the macros assume two decimalplaces. To get greater precision, select your data, chooseFormat-Cells-Number and specify the number of decimals:

To Create a Bar Chart:1. Highlight the labels and data to be graphed. Click on the


2. From the QI Macro Menu bar at the top of the page,select "Bar Chart." Shortcut: press Ctrl+Shift+B.

3. From the File Menu, select Save to save the graph.

4. Use the Chart Toolbar to Change the format of thegraph. Select Bar or Column for 2-dimensional columns orbars.

Select 3-D Bar for horizontal bars, and 3-D Column forvertical bars.

Bar Chart

29 QI Macros

To Create a Tree Diagram:The Tree Diagram is similar to a matrix. So, using Excel,you can use cells for each box and then connect them usingthe Drawing tools.

1. From the QI Macros Matrix and Diagram Selector,select the Tree diagram template: Treemtrx.xlt.

2. From the File Menu, select Save As to store the templateunder a new name.

3. Select a cell and type to put text in the cells. Then, usethe Border tool to put a box around each cell and line toolsfrom the Drawing Tool Bar to add lines.

4. Excel 97 and newer versions: Select VIEW-TOOLBARS-DRAWING. Use Autoshapes to find connecting lines.

To Create a Pie Chart:1. Highlight the labels and data to be graphed. Click on the


2. From the QI Macro Menu bar at the top of the page,select "Pie Chart." Shortcut: press Ctrl+Shift+O.

3. From the File Menu, select Save to save the graph withyour workbook.

4. Use the Chart Toolbar to change the format of thegraph. Select Pie for 2-dimensional pies. Select 3-D Piefor 3-D pie charts.

QI Macros 8

Pie Chart Tree Diagram

To Create a Scatter Chart:1. Highlight the labels and data to be graphed (as shown).

Click on the top left cell and drag the mouse across anddown to include the cells on the right.

2. From the QI Macro Menu bar, select "Scatter Dia-gram." Shortcut: press Ctrl+Shift+S.


4. To add new data to an existing chart see page 22.

To Create a Flow Chart:1. From the QI Macros Pull-Down Menu, select Matrix

and Diagram Selector. Choose the Flow Chart template.


3. Copy and paste the existing text, boxes, diamonds andarrows to create your flow chart. Change the text.

4. Excel 97 and newer versions: Select VIEW-TOOLBARS-DRAWING. Use Autoshapes to find more drawing shapesand to connect the boxes and diamonds as you would withany flowcharting tool.

9 QI Macros

-0.2

0

0.2

0.4

0.6

0.8

1

1.2

0 500 1000 1500 2000 2500 3000 3500 4000 4

Scatter Chart

QI Macros 28

To Create an Ishikawa:Excel may not be the best tool to do this with, but you caneasily draw Ishikawa diagrams with the drawing tools.

1. From the QI Macros Pull-Down Menu, select Matrixand Diagram Selector. Choose the cause-effect, orfishbone, template: Ishikawa.xlt.


3. Use the text and arrow tools from theDrawing Tool Bar to add arrows and causes. (To see thetool bar, select: View/Options-Toolbars-Drawing) Or justcopy and paste the existing arrows and text.

Use the Ellipse tool to circle root causes.

Each line, box, text, or circle is calledan "object." Objects can be: groupedtogether to form a single object ormoved in front or behind each otherusing the Drawing Tool.

To copy the fishbone and place it inanother document, use Cntrl+Shift+Ato select all objects, then Edit-Copy.

QI Macros 10

To Create a Histogram:1. Make sure the cells are formatted to the correct decimal

precision. From Format-Cells, select Number and specifythe number of decimal places you want.

2. Highlight the labels and data to be graphed (as shown,minimum of 20 data points required). Click on the top datacell and drag the mouse down to include just the data cells.

3. From the QI Macro Menu bar, select either the histo-gram (Ctrl+Shift+H) or frequency histogram(Ctrl+Shift+F) macro. The histogram will ask for upperand lower spec limits, the number of bars to display, and aunit of measure (1,.1,.5,.05) and specification limits:

27 QI Macros

If your data is 4.7, 4.8, etc., use .1.If your data is 4.75, 4.80, 4.85, use .05.

Histogram

LSL USL

Cause-Effect Diagram

Selecting a Template:Getting the right template depends on what kind processyou're using–planning, problem solving, process manage-ment, reliability, QFD, etc. Another way to open a QItemplate uses the Matrix and Diagram Selector:

1. From the QI Macro Menu bar, choose the "Matrix andDiagram Selector" to display an interactive dialog box(below). Click on any template name to open a copy of thetemplate. Click on OK to open the selected name.

Note: Templates should be found in XLSTART (PC) or theExcel Startup Folder (Mac). Missing templates may causethis macro to halt. Select FILE-NEW to check for theexistence of each template.

Efficiency Option: To speed up loading the QI Macros, moveall of the templates (*.xlt) out of XLSTART into Excel'sTemplate folder. Then open the QI Macro templates fromthat folder when you want to use one. (The matrix anddiagram selector will not work.)

Histogram

0

0.5

1

1.5

2

2.5

3

3.5

4

4.5

3.9 4.0 4.1 4.2 4.3 4.4 4.5 4.6 4.7 4.8 4.9 5.0 5.1 5.2 5.3 5.4 5.5 5.6 5.7 5.8 5.9 6 6.1

Values

Freq

uenc

y of

Val

ues

n=19

LSL=4.0 USL=6.0Cp=1.36

Cpk=1.2

Sigma=0.24

Mean=5.1

Median=5.0

Mode=4.9

Cp(Rd2)=3.97

Cpk(Rd2)=3.98

Pp=1.36

Ppk=1.2

Max=5.6

Min=4.7

PPM=0

Zbench=3.82

CpUpper=1.28

CpkLower=1.48


3. Then, the macro will draw the graph for you.

4. To realign the mean, median, mode, USL or LSL: Themacro isn't smart enough to know exactly where to put theall of the indicators—mean, median, mode, upper andlower specification limits. So you may need to do it.

Arrows: Click on each arrow and drag it to the appropri-ate position. To extend an arrow, click on it, then clickon the handle at either end and extend the arrow.

Text: Click on each text box and drag it to sit on top orbeside its corresponding arrow.

Spec Limits: To change upper and lower spec limits,click and drag the arrow to the appropriate place.Change the text and drag the spec limit to the arrow.


6. To revise the process capability, switch to the datasheet.Type the changed upper and lower specification limits intothe cells provided and Excel will recalculate Cp and Cpk:

To Create a Multivari orBox and Whisker Chart:

1. Highlight the labels and data to be graphed. Click on thetop left cell and drag the mouse across and down to includethe cells on the right.

2. From the QI Macro Menu bar at the top of the page,select "Multivari Chart." Shortcut: press Ctrl+Shift+Z or"Box and Whisker." Shortcut: press Ctrl+Shift+W.


25 QI Macros

3. Excel will load the template. From the File Menu, SelectSave As to save the template under a new name.

4. Add (copy/paste), change, or delete columns and rows tomeet your needs. Enter your data into the appropriatecells. You can put formulas in any cells to multiply weight-ing criteria as shown in Countermeasures matrix above orQFD matrix:

5. From the Options Menu (Macintosh) or View Menu (PC),select Toolbars—Drawing (see above) to bring up thedrawing toolbar. You can then add graphics, arrows orother objects to your matrix.

QI Macros 12

Drawing Tools

Box&Whisker

0.85 0.85

0.8

0.75

0.85 0.85

0.8 0.8

0.85

0.8

0.9

0.85

0.75

0.85

0.9

0.8

0.85

0.75

0.85

0.65

0.8 0.8 0.8

0.7 0.7

0.65 0.65

0.7

0.6

0.65

0.6

0.65

0.6

0.65

0.6

0.5

0.65

0.7

0.6

0.75

0.65

0.7

0.6

0.65

0.6

0.5

0.6

0.65

0.6 0.6

0.45

0.5

0.55

0.6

0.65

0.7

0.75

0.8

0.85

0.9

6/8

8am

10am

12pm 2pm

6/9

8am

10am

12pm 2pm

6/10

8am

10am

12pm 2pm

6/11

8am

10am

12pm 2p

m

6/12

8am

10am

12pm 2p

m

6/15

8am

10am

12pm 2p

m

6/16

8am

Mea

sure

men

t

Multivari Chart

0.45

0.5

0.55

0.6

0.65

0.7

0.75

0.8

0.85

0.9

6/8

8am

10am

12pm 2pm

6/9

8am

10am

12pm 2pm

6/10

8am

10am

12pm 2pm

6/11

8am

10am

12pm 2pm

6/12

8am

10am

12pm 2pm

6/15

8am

10am

12pm 2pm

6/16

8am

Mea

sure

men

t

n=90

13 QI Macros

To Create a Matrix:Six Sigma relies heavily on matrices to display relation-ships among many different criteria. Planning uses Voiceof the Customer (VOC) and Targets and Means matrices.Problem Solving uses Countermeasures and related matri-ces. Process Management uses Value-Added Analysis.Reliability uses FMEA and EMEA. QFD uses the House ofQuality and Pugh Concept selection matrices.

2. From the QI Macros pull down menu, choose matrixselector (pg. 26). A number of different matrix templateswill be displayed:

Planning Arrow Arrow DiagramCostofQ Cost of (Poor) Quality MatrixRelation Relationship/System DiagramTargetMn Targets and Means MatrixTreeMtrx Tree DiagramVOCmtrx Voice of the Customer Matrix

Problem Action Action Plan Solving Checksht Checksheet

CMmatrix Countermeasures MatrixForcefld Force Field AnalysisIshikawa Ishikawa (Cause-Effect) Diagram

Process FlowChrt Flow Chart Mgt. Measures Requirements-Measurements Matrix

ValueAdd Value Added Flow Analysisc, np, p, u Attribute Control ChartsXmRchart Individuals & Moving RangeXbarRchart Average and RangeXandSchart Average and Standard Deviation

DFSS DOE Design of ExperimentsPughmtrx Pugh Concept Selection MatrixQFDhouse QFD House of Quality

ReliabilityEMEA Error Modes and Effects AnalysisFMEA Failure Modes and Effects AnalysisFaultree Fault Tree Diagram

Selecting a Control Chart:Choosing the right chart depends on your data–attribute(counted) or variable (measured)–and the sample size.

see: qimacros.com/industry.htmlNumber in SampleType of data 1 2-or-more VariesFraction Defective np pNumber of defects c uTime, length, weight, $ XmR XbarR (Measured) XandS

1. To draw a control chart,highlight the labels and data to begraphed (as shown). You will need 20 or more data pointsto get a good graph.

2. From the QI Macro Menu bar, select the type of controlchart or "Control Chart Selector" to display the dialogbox (below). Click on the buttons to choose the type of dataand chart. Click OK to draw the control chart.

QI Macros 24

Matrix

To Create an XmR Chart:An XmR chart—the Individuals and Moving Range Chart–tracks individual values of continuous data. The XmRTrendChart tracks individual values of data which may contain atrend such as increasing costs or decreasing sales revenue.

1. Highlight the labels and data to be graphed (as shown).You will need 20 or more data points to get a good graph.

2. From the QI Macro Menu bar, select "XmR Chart."Shortcut: press Ctrl+Shift+R. The macro will create a newworksheet and begin calculating the X and R values,control limits, and averages (see below).

3. The macro will first draw the Range Chart. If the Rchart looks unstable, then the process is often unstable.


Excel will expand the graph from 20 to 25 points:

5. To extend and recalculate the limits, change the averageformula on the data worksheet. Only change the new datarows 22-26: AVERAGE($B$22:$B$26).

The formulas will adjust the UCL and LCL. Add textvalues for the new UCL, CL, and LCL:

Control ChartC Chart Title

0

2

4

6

8

10

12

14

16

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

Sheet Number

Num

ber

of P

inho

les

n=152UCL=15.87

CL=7.60

C Chart Title

0

2

4

6

8

10

12

14

16

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

Sheet Number

Num

ber

of P

inho

les

n=152UCL=15.87

CL=7.60

CL=3

UCL=8.19

new points

new points


Adding Data To Control Charts:

To add new data to an existing graph:

1. Open the saved workbook and chart. On the intermediateworksheet (e.g., cdata1), add the new data.

Note: on a control chart, use copy/paste to extend UCL,LCL, and CL alongside the new data.

To show process changes, simply recalculate the averageand the UCL, LCL will automatically change.

2. Switch sheets by selecting the Chart sheet. Then either:• Click on the Chart menu and select Source Data or• Chart Wizard button then the chart:

3. Either one will give you a place to change the range.Change the last number of each range to include the newrows of data (e.g., $A$21 becomes $A$26, $E$21 be-comes $E$26).

4. Click on Okay or Finish to add data to the graph.

Macros now show potentially unstable conditions in red.

4. Next, the macro will draw the X chart. If the RangeChart looks stable and the X chart is stable, then the pro-cess is stable.


6. For information on interpreting Control Charts, see SixSigma Simplified (page 83) or www.qimacros.com/qiwizard/stability.html.

7. To add new data to a control chart see page 22.

Hours Worked (Range)

RUCL=18.05

CL=5.52

0

2

4

6

8

10

12

14

16

18

20

1/5/96

1/19/9

62/2

/96

2/16/9

6

3/1/96

3/15/9

6

3/29/9

6

4/12/9

6

4/26/9

6

5/10/9

6

5/24/9

6

6/7/96

6/21/9

6

7/5/96

7/19/9

6

8/2/96

8/16/9

6

8/30/9

6

9/13/9

6

9/27/9

6

10/11

/96

10/25

/96

Date

Num

ber

RangeUCL+2 sigma+1 sigmaAverage-1 sigma-2 sigmaLCL

n=21

Hours Worked (X Chart)

UCL=24.69

CL=10.00

LCL=-4.69

-10

-5

0

5

10

15

20

25

30

1/5/

96

1/19

/96

2/2/

96

2/16

/96

3/1/

96

3/15

/96

3/29

/96

4/12

/96

4/26

/96

5/10

/96

5/24

/96

6/7/

96

6/21

/96

7/19

/96

8/2/

96

8/16

/96

8/30

/96

9/13

/96

9/27

/96

10/1

1/96

10/2

5/96

Date

Num

ber

of H

ours Hours Missed

UCL+2 sigma+1 sigmaAverage-1 sigma-2 sigmaLCL

n=22

Chart-Source Data

ChartWizard


To Create an XR Chart:The XbarR—Average and Range Chart—tracks smallsamples of continuous data. The X and S chart tracks largesamples (10 or more). Both work for 2-25 samples.

1. Highlight the labels and data to be graphed (as shown).Click on the top left cell and drag the mouse across anddown to include the cells on the right. You will need 25 ormore subgroups of 2-25 samples to get a good graph. TheXbarR macro uses the Range (R); The XandS macro usesthe standard deviation (s).

2. From the QI Macro Menu bar, select "XbarR Chart."Shortcut: press Ctrl+Shift+X. The macro will create a newworksheet and begin calculating the X and R values, controllimits, and averages (below). (Use Ctrl+Shift+V for X&S.)

Control Chart To Create a u Chart:A u chart tracks the number of defects—non-conformi-ties—in varying sample sizes (e.g., billing errors permonth, errors per bill).

1. Highlight the labels and data to be graphed (as shown).

2. From the QI Macro Menu bar at the top of the page,select "u Chart." Shortcut: press Ctrl+Shift+U.


3. To change the style of each line on the graph, doubleclick on each line.



Control Chart

Non-Conforming Widgets

CL=0.64

0

0.5

1

1.5

2

2.5

3

1/5/

96

1/19

/96

2/2/

96

2/16

/96

3/1/

96

3/15

/96

3/29

/96

4/12

/96

4/26

/96

5/10

/96

5/24

/96

6/7/

96

6/21

/96

7/5/

96

7/19

/96

8/2/

96

8/16

/96

8/30

/96

9/13

/96

9/27

/96

Date

Num

ber

Non

-Con

form

ing

UUCL+2 sigma+1 sigmaAverage-1 sigma-2 sigmaLCL

n=198

3. The macro will first draw the Range Chart. If the Rchart looks unstable, then the process is often unstable.


4. Next, the macro will draw the X chart. If the RangeChart is stable and the X chart is stable, then the process isstable although possibly not "capable."


6. For information on interpreting Control Charts, see SixSigma Simplified (page 83) or www.qimacros.com/qiwizard/stability.html.


To Create a p Chart:A p chart tracks the fraction defective in varying samples(e.g., fraction of commitments missed per week).


2. From the QI Macro Menu bar at the top of the page,select "p Chart." Shortcut: press Ctrl+Shift+P.

3. To change the style of any line on the graph, double clickon the line.



Control Chart


Gauge Readings (Range)

RUCL=0.38

CL=0.18

0

0.05

0.1

0.15

0.2

0.25

0.3

0.35

0.4

0.45

6/8 8a

m10

am12

pm 2pm

6/9 8a

m10

am12

pm 2pm

6/10 8

am 10am

12pm 2p

m

6/11 8

am10

am12

pm 2pm

6/12 8

am 10am

12pm 2p

m

6/15 8

am10

am12

pm 2pm

6/16 8

am

Time

Ran

ge

RangeUCL+2 sigma+1 sigmaAverage-1 sigma-2 sigmaLCL

n=25

Gauge Readings

UCL=0.82

CL=0.72

LCL=0.61

0.6

0.65

0.7

0.75

0.8

0.85

6/8 8a

m10

am12

pm 2pm

6/9 8a

m10

am12

pm 2pm

6/10 8

am 10am

12pm 2p

m

6/11 8

am 10am

12pm 2p

m

6/12 8

am 10am

12pm 2p

m

6/15 8

am 10am

12pm 2p

m

6/16 8

am

Time

Rea

ding

s

XAverageUCL+2 sigma+1 sigmaAverage-1 sigma-2 sigmaLCL

n=125

Non-Conforming Magnets

CL=0.07

0.03

0.04

0.05

0.06

0.07

0.08

0.09

0.1

0.11

0.12

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Period

Frac

tion

Def

ectiv

e PUCL+2 sigma+1 sigmaAverage-1 sigma-2 sigmaLCL

n=1,030

Defective Widgets

UCL=6.18

CL=2.08

0

1

2

3

4

5

6

7

8

9

1/19

/96

2/2/

96

2/16

/96

3/1/

96

3/15

/96

3/29

/96

4/26

/96

5/10

/96

5/24

/96

6/7/

96

6/21

/96

7/5/

96

7/19

/96

8/2/

96

8/16

/96

8/30

/96

9/27

/96

10/1

1/96

10/2

5/96

11/8

/96

11/2

2/96

12/6

/96

Date

Num

ber D

efec

tive Defective

UCL+2 sigma+1 sigmaAverage-1 sigma-2 sigmaLCL

n=52

To Create a c Chart:A c chart tracks the number of defects when the number issmall compared to the total possible (e.g., injuries/week).


2. From the QI Macro Menu bar, select "c Chart."Shortcut: press Ctrl+Shift+C.

3. To change the style of each line on the graph, doubleclick on each line.


5. For information on interpreting Control Charts, see SixSigma Simplified (page 83) or www.qimacros.com/qiwizard/stability.html. To add new data to a controlchart see page 22.

To Create an np Chart:An np chart tracks the number of defective or non-con-forming items when subgroup sample sizes are constant(e.g., defective parts in 100 sampled).


2. From the QI Macro Menu bar, select "np Chart."Shortcut: press Ctrl+Shift+N. The macro will ask forsample size:




Control ChartControl Chart


Pinholes in Sheets

UCL=16.49

CL=8.00

0

2

4

6

8

10

12

14

16

18

20

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

Sheet Number

Num

ber

oF P

inho

les

No. PinholesUCL+2 sigma+1 sigmaAverage-1 sigma-2 sigmaLCL

n=200

QI MAcros for Excel.pdf

Documents

Transcript of QI MAcros for Excel.pdf