1. a.Construct the appropriate cont

1. a.Construct the appropriate control charts for these
data.
b.Is the process in a state of statistical control?
Explain.
c. What should the team recommend as the next step to
improve the process?
Trang 754
1. Arndt, M., “Quality Isn’t Just for Widgets,”
BusinessWeek, July 22, 2002, pp. 72–73.
2. Automotive Industry Action Group (AIAG),Statistical
Process Control Reference Manual(Chrysler, Ford, and
General Motors Quality and Supplier Assessment Staff,
1995).
3. Bothe, D. R.,Measuring Process Capability(New York:
McGraw-Hill, 1997).
4. Cyger, M., “The Last Word—Riding the Bandwagon,”
iSixSigma Magazine, November/December 2006.
5. Davis, R. B., and T. C. Krehbiel, “Shewhart and Zone
Control Charts Under Linear Trend,”Communications in
Statistics: Simulation and Computation, 31 (2002),
91–96.
6. Deming, W. E.,The New Economics for Business, Indus-try, and Government(Cambridge, MA: MIT Center for
Advanced Engineering Study, 1993).
7. Deming, W. E.,Out of the Crisis(Cambridge, MA: MIT
Center for Advanced Engineering Study, 1986).
8. Gabor, A.,The Man Who Discovered Quality(New York:
Time Books, 1990).
9. Gitlow, H., and D. Levine,Six Sigma for Green Belts and
Champions (Upper Saddle River, NJ: Financial
Times/Prentice Hall, 2005).
10. Gitlow, H., D. Levine, and E. Popovich,Design for Six
Sigma for Green Belts and Champions(Upper Saddle
River, NJ: Financial Times/Prentice Hall, 2006).
11. Hahn, G. J., N. Doganaksoy, and R. Hoerl, “The Evolu-tion of Six Sigma,” Quality Engineering, 12 (2000),
317–326.
12. Lemak, D. L., N. P. Mero, and R. Reed, “When Quality
Works: A Premature Post-Mortem on TQM,”Journal of
Business and Management, 8 (2002), 391–407.
13. Levine, D. M.,Statistics for Six Sigma for Green Belts
with Minitab and JMP(Upper Saddle River, NJ: Finan-cial Times/Prentice Hall, 2006).
14.Microsoft Excel 2010(Redmond, WA: Microsoft Corp.,
2010).
15.Minitab Release 16(State College, PA: Minitab Inc.,
2010).
16. Scherkenbach, W. W.,The Deming Route to Quality and
Productivity: Road Maps and Roadblocks(Washington,
DC: CEEP Press, 1987).
17. Shewhart, W. A.,Economic Control of the Quality of
Manufactured Product(New York: Van Nostrand-Reinhard, 1931, reprinted by the American Society for
Quality Control, Milwaukee, 1980).
18. Snee, R. D., “Impact of Six Sigma on Quality,”Quality
Engineering, 12 (2000), ix–xiv.
19. Vardeman, S. B., and J. M. Jobe,Statistical Methods for
Quality Assurance: Basics, Measurement, Control, Ca-pability and Improvement(New York: Springer-Verlag,
2009).
20. Walton, M.,The Deming Management Method(New
York: Perigee Books, 1986).
EG17.1 The THEORY of CONTROL CHARTS
There are no Excel Guide instructions for this section.
EG17.2 CONTROL CHART for the
PROPORTION: ThepCHART
PHStat2UsepChartto create a chart and supporting
worksheets that compute the control limits and plot points.
For example, to create the Figure 17.2 chart for the Table
17.1 nonconforming hotel room data on page 722, open to
the DATA worksheetof theHotel1 workbook. Select
PHStat➔Control Charts➔pChartand in the proce-dure’s dialog box (shown below):
1. EnterC1:C29as the Nonconformances Cell Range.
2. CheckFirst cell contains label.
3. ClickSize does not vary and enter 200 as the
Sample/Subgroup Size.
4. Enter a Titleand click OK.
p
p
Sample/Subgroup Cell Range, and clickFirst cell contain
label.
In-Depth Excel Use thepChartDATAandCOMPUTE
worksheetsof thep Chart workbookas a template for
computing control limits and plot points. The pChart-DATA worksheet uses formulas in column D that divide
the column C number of nonconformances value by the
column B subgroup/sample size value to compute the
proportion and uses formulas in columns E through
G to display the values for the LCL, and UCL that are
computed in cells B12 through B14 of the COMPUTE
worksheet. In turn, the COMPUTE worksheet (shown be-low) uses the subgroup sizes and the proportion values
found in the pChartDATA worksheet to compute the con-trol limits.
p,
1pi2
CHAPTER 17 EXCEL GUIDE
755
The procedure creates a chart on its own chart sheet and
two supporting worksheets: one that computes the control
limits and one that computes the values to be plotted. For
more information about these two worksheets, read the fol-lowingIn-Depth Excelinstructions.
For problems in which the sample/subgroup sizes vary,
replace step 3 with this step: ClickSize varies, enter the cell
range that contains the sample/subgroup sizes as the
p
Computing control limits and plotting points for other
problems requires changes to thepChartDATA worksheet
of thep Chart workbook. First, paste the time period, sub-group/sample size, and number of nonconformances data
into columns A through C of the pChartDATA worksheet. If
there are more than 28 time periods, select cell range
D29:G29and copy the range down through all the rows. If
there are fewer than 28 time periods, delete the extra rows
from the bottom up, starting with row 29.
Use the pChartDATA worksheet as the basis for creat-ing apchart. For example, to create the Figure 17.2 chart
for the nonconforming hotel room data on page 722, open
to the pChartDATA worksheet which contains the Table 17.1
nonconforming hotel room data on page 722. Select the cell
trang 757
Computing control limits and plotting points for other
problems requires changes to thecChartDATA worksheet
of thec Chart workbook. First, paste the time period and
number of nonconformances data into columns A and B of
the cChartDATA worksheet. If there are more than 50 time
periods, select cell rangeC51:E51and copy the range
down through all the rows. If there are fewer than 50 time
periods, delete the extra rows from the bottom up, starting
with row 51.
Use the cChartDATA worksheet as the basis for creat-ing acchart. For example, to create the Figure 17.5cchart
for the hotel complaint data on page 729, open to the
cChartDATA worksheet which contains the Table 17.4 hotel
complaint data on page 729. Select the cell rangeB1:E51
and:
1. SelectInsert➔Scatterand select the fourth choice
from theScattergallery(Scatter with Straight Lines
and Markers).
2. Relocate the chart to a chart sheet and adjust the chart
formatting by using the instructions in Appendix Sec-tion F.4 on page 815.
At this point, a recognizable chart begins to take shape, but
the control limit and center lines are improperly formatted
and are not properly labeled. To correct these formatting er-rors, use the three sets of instructions given in the Section
EG17.2In-Depth Excelinstructions.
EG17.5 CONTROL CHARTS for the RANGE
and the MEAN
TheRChart and the Chart
PHStat2UseR and XBar Chartsto create Rand
charts and supporting worksheets that compute the control
X
X
Chapter 17 Excel Guide 757
limits and plot points. For example, to create the Figure
17.6Rchart and the Figure 17.7 chart for the Table 17.5
luggage delivery times (see pages 734 and 735), open to
the DATA worksheetof theHotel2 workbook. Because
the PHStat2 procedure requires column cell ranges that
contain either means or ranges, first add two columns that
compute the mean and ranges on this worksheet. Enter the
column headingMeanin cellG1and the headingRange
in cellH1. Enter the formula =AVERAGE(B2:F2)in cell
G2and the formula=MAX(B2:F2) - MIN(B2:F2)in cell
H2. Select the cell range G2:H2and copy the range down
through row 29.
With the two columns created, selectPHStat➔Con-trol Charts➔R and XBar Charts. In the procedure’s dia-log box (shown below):
1. Enter5as the Subgroup/Sample Size.
2. EnterH1:H29as the Subgroup Ranges Cell Range.
3. CheckFirst cell contains label.
4. ClickR and XBar Charts. Enter G1:G29as the
Subgroup Means Cell Rangeand checkFirst cell
contains label.
5. Enter a Titleand click OK.
X
The procedure creates the two charts on separate chart
sheets and two supporting worksheets: one that computes
the control limits and one that computes the values to be
plotted. For more information about these two worksheets,
read the followingIn-Depth Excelsection
trang 757
Computing control limits and plotting points for other
problems requires changes to thecChartDATA worksheet
of thec Chart workbook. First, paste the time period and
number of nonconformances data into columns A and B of
the cChartDATA worksheet. If there are more than 50 time
periods, select cell rangeC51:E51and copy the range
down through all the rows. If there are fewer than 50 time
periods, delete the extra rows from the bottom up, starting
with row 51.
Use the cChartDATA worksheet as the basis for creat-ing acchart. For example, to create the Figure 17.5cchart
for the hotel complaint data on page 729, open to the
cChartDATA worksheet which contains the Table 17.4 hotel
complaint data on page 729. Select the cell rangeB1:E51
and:
1. SelectInsert➔Scatterand select the fourth choice
from theScattergallery(Scatter with Straight Lines
and Markers).
2. Relocate the chart to a chart sheet and adjust the chart
formatting by using the instructions in Appendix Sec-tion F.4 on page 815.
At this point, a recognizable chart begins to take shape, but
the control limit and center lines are improperly formatted
and are not properly labeled. To correct these formatting er-rors, use the three sets of instructions given in the Section
EG17.2In-Depth Excelinstructions.
EG17.5 CONTROL CHARTS for the RANGE
and the MEAN
TheRChart and the Chart
PHStat2UseR and XBar Chartsto create Rand
charts and supporting worksheets that compute the control
X
X
Chapter 17 Excel Guide 757
limits and plot points. For example, to create the Figure
17.6Rchart and the Figure 17.7 chart for the Table 17.5
luggage delivery times (see pages 734 and 735), open to
the DATA worksheetof theHotel2 workbook. Because
the PHStat2 procedure requires column cell ranges that
contain either means or ranges, first add two columns that
compute the mean and ranges on this worksheet. Enter the
column headingMeanin cellG1and the headingRange
in cellH1. Enter the formula =AVERAGE(B2:F2)in cell
G2and the formula=MAX(B2:F2) - M

Kết quả (Anh) 2:

1. A.Construct the Appropriate control Charts for These
Data.
b.Is in a State of the Statistical Process control?
Explain.
c. Recommend What Should the team as the next step to
Improve the Process?
Trang 754
1. Arndt, M ., "Quality Is not Just for Widgets,"
BusinessWeek, July 22, 2002, PP. 72-73.
2. Automotive Industry Action Group (AIAG), Statistical
Process Control Reference Manual (Chrysler, Ford, and
General Motors Quality and Supplier Assessment Staff,
1995).
3 Bothe, DR, Measuring Process Capability (New York:.
. McGraw-Hill, 1997)
. 4 Cyger, M., "The Last Word-Riding the Bandwagon,"
iSixSigma Magazine, November / December . 2006
5 Davis, RB, and TC Krehbiel, "Shewhart and Zone.
Control Charts Under Linear Trend, "Communications in
Statistics: Simulation and Computation, 31 (2002),
. 91-96
. 6 Deming, WE, The New Economics for Business, Indus-TRY, and Government (Cambridge, MA: MIT Center for
Advanced Engineering Study, 1993).
7 Deming, WE, Out of the Crisis. (Cambridge, MA: MIT
. Center for Advanced Engineering Study, 1986)
8. gabor, A., The Man Who Discovered Quality (New York:
Time Books, 1990).
9 Gitlow, H., and D. Levine, Six Sigma for Green Belts and.
Champions (Upper Saddle River, NJ: Financial
Times / Prentice Hall, 2005).
10 Gitlow, H., D. Levine, and E. Popovich, Design for Six.
Sigma for Green Belts and Champions (Upper Saddle
River, NJ:. Financial Times / Prentice Hall, 2006)
. 11 ​​Hahn, GJ, N. Doganaksoy, and R. Hoerl, "The Evolu-tion of Six Sigma," Quality Engineering, 12 (2000),
317-326.
12. Lemak, DL, NP Mero, and R. Reed, "When Quality
works: A Premature Post-Mortem on TQM, "Journal of
Business and Management, 8 (2002), 391-407.
13 Levine, DM, Statistics for Six Sigma for Green Belts.
with Minitab and JMP (Upper Saddle River, NJ: Finan-cial Times / Prentice Hall, 2006).
14.Microsoft Excel 2010 (Redmond, WA: Microsoft Corp.,.
2010).
15.Minitab Release 16 (State College, PA:. Minitab Inc.,
. 2010)
16 Scherkenbach,. WW, The Deming Route to Quality and
Productivity: Road Maps and Roadblocks (Washington,
DC: CEEP Press, 1987).
17 Shewhart, WA, Economic Control of the Quality of.
Manufactured Product (New York: Van Nostrand-Reinhard, 1931, reprinted by the American Society for
Quality Control, Milwaukee, 1980).
18. Snee, RD, "Impact on Quality of Six Sigma," Quality
Engineering, 12 (2000), ix-xiv.
19. Vardeman, SB, and JM Jobe , Statistical Methods for
Quality Assurance: Basics, Measurement, Control, Ca-pability and Improvement (New York: Springer-Verlag,
2009).
. 20 Walton, M., The Deming Management Method (New
York: Perigee Books, 1986).
EG17.1 The THEORY of CONTROL CHARTS
There are no instructions for this Excel Guide Section.
EG17.2 CONTROL CHART for the
PROPORTION: ThepCHART
PHStat2UsepChartto Create a Chart and Supporting
worksheets that Compute the control limits and Plot points.
For Example, to Create the Figure 17.2 Chart for the Table
17.1 nonconforming hotel room Data on Page 722, Open to
. the DATA worksheetof theHotel1 workbook Select
PHSTAT ➔ Control Charts ➔ pChartand in the proce-dure's Dialog Box (shown below):
. 1 EnterC1: C29as the Nonconformances Cell Range.
2. checkFirst cell contains label.
3. ClickSize does Not Vary and ENTER 200 as the
Sample / Subgroup Size.
4. Enter a Titleand click OK.
P
P
Sample / Subgroup Cell Range, and clickFirst cell contain
label.
In-Depth Excel Use thepChartDATAandCOMPUTE
worksheetsof Thep Chart workbookas a template for
Computing control limits and Plot points. The pChart-DATA worksheet uses formulas in column D that divide
the column C Number of nonconformances value by the
column B subgroup / Sample Size value to Compute the
Proportion and uses formulas in columns E through
G to display the values ​​for the LCL, and UCL that are
computed in cells B12 through B14 of the COMPUTE
worksheet. In Turn, the COMPUTE worksheet (shown be-low) uses the subgroup sizes and the Proportion values
found in the pChartDATA worksheet to Compute the con-trol limits.
P,
1pi2
EXCEL GUIDE CHAPTER 17
755
The procedure creates a Chart Chart sheet and on its Own
Two Supporting worksheets: One that computes the control
limits and One that computes the values ​​to be . Plotted For
More Information about These Two worksheets, Read the fol-lowingIn-Depth Excelinstructions.
For problems in which the Sample / subgroup sizes Vary,
replace step 3 with this step: ClickSize varies, ENTER the cell
that contains the Sample Range / subgroup sizes as the
P
Computing control limits and Plotting points for Other
problems requires changes to thepChartDATA worksheet
of Thep Chart workbook. First, the time period Paste, sub-group/sample Size, and Number of nonconformances Data
into columns A through C of the pChartDATA . worksheet If
there are More than 28 time periods, Range Select cell
D29:. G29and Copy the Range down through all the rows If
there are Fewer than 28 time periods, the Extra delete rows
from the bottom up, starting with row 29.
Use the pChartDATA worksheet as the basis for creat-ing apchart. For Example, to Create the Chart Figure 17.2
Data for the nonconforming hotel room on Page 722, Open
to the pChartDATA worksheet which contains the Table 17.1
nonconforming hotel room Data on Page 722. Select the cell
trang 757
Computing control limits and Plotting points for Other
problems requires changes to thecChartDATA worksheet
of tHEC Chart workbook. First, the time period and Paste
Number of nonconformances Data into columns A and B of
the cChartDATA worksheet. If there are More than 50 time
periods, Select cell rangeC51: E51and Copy the Range
. down through all the rows If there are Fewer than 50 time
periods, the Extra delete rows from the bottom up, starting
with row 51.
cChartDATA Use the worksheet as the basis for creat- ing acchart For Example, to Create the Figure 17.5cchart.
Complaint Data for the hotel on Page 729, Open to the
cChartDATA worksheet which contains the Table 17.4 hotel
Complaint Data on Page 729 Select the cell rangeB1:. E51
and:
. 1 SelectInsert ➔ Scatterand Select the fourth Choice
from theScattergallery (Scatter with Straight Lines
and Markers).
2. Relocate the Chart to a Chart sheet and adjust the Chart
formatting by using the instructions in Appendix F.4 Sec-tion on Page 815.
At this Point, Take a Recognizable Chart begins to shape, but
the control limit and center lines are Improperly formatted
and are Not properly LABELED. To correct er-rors These formatting, use the three sets of instructions given in the Section
EG17.2In-Depth Excelinstructions.
EG17 RANGE CONTROL CHARTS for the .5
and the MEAN
TheRChart and the Chart
PHStat2UseR and XBar Chartsto Create Rand
Charts and Supporting worksheets that Compute the control
X
X
Chapter 17 Excel Guide 757
limits and Plot points. For Example, to Create the Figure
17.6Rchart and the Figure 17.7 Chart for the Table 17.5
Luggage delivery times (see Pages 734 and 735), Open to
the DATA worksheetof theHotel2 workbook. Because
the column cell Ranges PHSTAT2 procedure requires that
contain either means or Ranges, first add Two columns that
Compute the mean and Ranges on this worksheet Enter the.
column headingMeanin cellG1and the headingRange
in cellH1 Enter the Formula = AVERAGE (B2: F2) in cell.
G2and the Formula = MAX (B2: F2) - MIN (B2: F2) in cell
H2 Select. the cell Range G2: H2and Copy the Range down
through row 29.
With the Two columns Created, selectPHStat ➔ Con-trol Charts ➔ R and XBar Charts In the procedure's dia-log Box (shown below):.
. 1 Enter5as the Subgroup / . Sample Size
. 2 EnterH1:. H29as the Subgroup Ranges Cell Range
3 checkFirst cell contains label..
4 Clickr and XBar Charts Enter G1:.. G29as the
Subgroup Means Cell Rangeand checkFirst cell
. contains label
5 Enter a Titleand click OK..
X
The procedure creates the Two Charts Chart on separate
sheets and Two Supporting worksheets: One that computes
the control limits and One that computes the values ​​to be
Plotted For More Information about These Two worksheets,.
Read the followingIn-Depth Excelsection
trang 757
Computing control limits and Plotting points for Other
problems requires changes to thecChartDATA worksheet
of tHEC Chart workbook. First, the time period and Paste
Number of nonconformances Data into columns A and B of
the cChartDATA worksheet. If there are More than 50 time
periods, Select cell rangeC51 : E51and Copy the Range
. down through all the rows If there are Fewer than 50 time
periods, the Extra delete rows from the bottom up, starting
with row 51.
cChartDATA Use the worksheet as the basis for creat-ing acchart For Example,. to Create the Figure 17.5cchart
for the hotel Complaint Data on Page 729, Open to the
cChartDATA worksheet which contains the Table 17.4 hotel
Complaint Data on Page 729 Select the cell rangeB1:. E51
and:
. 1 SelectInsert ➔ Scatterand Select the fourth Choice
from theScattergallery (Scatter with Straight Lines
and Markers).
2. Relocate the Chart to a Chart sheet and adjust the Chart
formatting by using the instructions in Appendix F.4 Sec-tion on Page 815.
At this Point, a Recognizable Chart begins to Take shape, but
the control limit and center lines are Improperly formatted
and are Not properly LABELED. To correct er-rors These formatting, use the three sets of instructions given in the Section
EG17.2In-Depth Excelinstructions.
EG17.5 CONTROL CHARTS for the RANGE
and the MEAN
TheRChart and the Chart
PHStat2UseR and XBar Chartsto Create Rand
Charts and Supporting worksheets that Compute the control
X
X
Chapter 17 Excel Guide 757
limits and Plot points. For Example, to Create the Figure
17.6Rchart and the Figure 17.7 Chart for the table 17.5
Luggage delivery times (see Pages 734 and 735), Open to
the DATA worksheetof theHotel2 workbook. Because
the column cell Ranges PHSTAT2 procedure requires that
contain either means or Ranges, first add Two columns that
Compute the mean and Ranges on this worksheet. ENTER the
column headingMeanin cellG1and the headingRange
in cellH1 Enter the Formula = AVERAGE (B2: F2) in cell.
G2and the Formula = MAX (B2: F2) - M

Kết quả (Anh) 3:

1 a.Construct the appropriate control charts for these
data.
b.Is the process in a state of statistical control?
Explain.
c. What should the team recommend as the next step to
improve the process?
Trang 754
1. Arndt, M., "Quality Isn 't Just for Widgets," July
BusinessWeek, 22, 2002, pp. 72 - 73.
2. Automotive Industry Action Group (AIAG Control Reference Manual (Statistical
Process), Chrysler,Ford, and
General Motors Quality and Supplier Assessment Staff,
1995).
3. Bothe, D. R., Measuring Process Capability (New York:
McGraw-Hill, 1997).
4. Cyger, M., "The Last Word Riding the Bandwagon,"
iSixSigma Magazine, November/December 2006.
5. Davis, R. B., and T. C. Krehbiel, "Shewhart and Zone
Control Charts Under Linear Trend," Communications in
Statistics: Simulation and Computation,31 (2002),
91 - 96.
6. Deming, W. E., The New Economics for Business, Indus-try, and Government (Cambridge, MA: MIT Center for
Advanced Engineering Study, 1993).
7. Deming, W. E., Out of the Crisis (Cambridge, MA: MIT
Center for Advanced Engineering Study, 1986).
8. Gabor, A., The Man Who Discovered Quality (New York:
Time Books, 1990).
9. Gitlow, H., and D. Levine, Six Sigma for Green Belts and
Champions (Upper Saddle River, NJ: Financial
Times/Prentice Hall, 2005).
10. Gitlow, H., D. Levine, and E. Popovich, Design for Six
Sigma for Green Belts and Champions (Upper Saddle
River, NJ: Financial Times/Prentice Hall, 2006). 11 Hahn, G. J., N. Doganaksoy, and R. Hoerl, "The Evolu-tion of Six Sigma," Quality Engineering, 12 (2000),
317 - 326.
12. Lemak, D. L., N. P. Mero, and R. Reed,"When Quality
Works: A Premature Post-Mortem on TQM," Journal of
Business and Management, 8 (2002), 391 – 407.
13. Levine, D. M., Statistics for Six Sigma for Green Belts
with Minitab and JMP (Upper Saddle River, NJ: Finan-cial Times/ Prentice Hall, 2006).
14.Microsoft Excel 2010 (Redmond, WA: Microsoft Corp.,
2010).
15.Minitab Release 16 (State College, PA: Minitab Inc.,.
16. Scherkenbach, W.
2010)W., The Deming Route to Quality and
Productivity: Road Maps and Roadblocks (Washington,
DC: CEEP Press, 1987).
17. Shewhart, W. A., Economic Control of the Quality of
Manufactured Product (New York: Van Nostrand-Reinhard, 1931, reprinted by the American Society for
Quality Control, Milwaukee, 1980).
18. Snee, R. D., Impact of Six "Sigma on Quality," Quality
Engineering, 12 (2000), IX - xiv.
19Vardeman, S. B., and J. M. Jobe, Statistical Methods for
Quality Assurance: Basics, Measurement, Control, Ca-pability and Improvement (New York: Springer-Verlag,
2009).
20. Walton, M., The Deming Management Method (New
York: Perigee Books, 1986).
EG17.1 The THEORY of CONTROL CHARTS
There are no Excel Guide instructions for this section.
EG17.2 CONTROL CHART for the
PROPORTION: ThepCHART
PHStat2UsepChartto create a chart and supporting
worksheets that compute the control limits and plot points.
For example, to create the Figure 17.2 chart for the Table
17.1 nonconforming hotel room data on page 722, open to
the DATA worksheetof theHotel1 workbook. Select
PHStat Control Charts pChartand in 'the proce-dure s dialog box (shown below):
1. EnterC1C29as the Nonconformances Cell Range.
2. CheckFirst cell contains label.
3. ClickSize does not vary and enter 200 as the
Sample/Subgroup Size.
4. Enter a Titleand click OK.
p
p
Sample/Subgroup Cell Range, and clickFirst cell contain
label.
In-Depth Excel Use thepChartDATAandCOMPUTE
worksheetsof thep Chart workbookas a template for
computing control limits and plot pointsThe pChart-DATA worksheet uses formulas in column D that divide
the column C number of nonconformances value by the
column B subgroup/sample size value to compute the
proportion and uses formulas in columns E through
G to display the values for the LCL, and UCL that are
computed in cells B12 through B14 of the COMPUTE
worksheet. In turn,The COMPUTE worksheet (shown be-low) uses the subgroup sizes and the proportion values
found in the pChartDATA worksheet to compute the con-trol limits.
p,
1pi2
CHAPTER 17 EXCEL GUIDE
755
The procedure creates a chart on its own chart sheet and
two supporting worksheets: one that computes the control
limits and one that computes the values to be plotted. For
More information about these two worksheets, read the fol-lowingIn-Depth Excelinstructions.
For problems in which the sample/subgroup sizes vary,
replace step 3 with this step: ClickSize varies, enter the cell
range that contains the sample/subgroup sizes as the
p
Computing control limits and plotting points for other
problems requires changes to thepChartDATA worksheet
of thep Chart WorkbookFirst, paste the time period, sub-group/sample size, and number of nonconformances data
into columns A through C of the pChartDATA worksheet. If
there are more than 28 time periods, select cell range
D29:G29and copy the range down through all the rows. If
there are fewer than 28 time periods, delete the extra rows
from the bottom up, starting with row 29.
Use the pChartDATA worksheet as the basis for creat-ing apchart. For example, to create the Figure 17.2 chart
for the nonconforming hotel room data on page 722, open
to the pChartDATA worksheet which contains the Table 17.1
nonconforming hotel room data on page 722 Select the cell
trang 757
Computing control limits and plotting points for other
Problems requires changes to thecChartDATA worksheet
of thec Chart workbook. First, paste the time period and
number of nonconformances data into columns A and B of
the cChartDATA worksheet. If there are more than 50 time
periods, select cell rangeC51:E51and copy the range
down through all the rows. If there are fewer than 50 time
periods, delete the extra rows from the bottom up, starting
With row 51.
Use the cChartDATA worksheet as the basis for creat-ing acchart. For example, to create the Figure 17.5cchart
for the hotel complaint data on page 729, open to the
cChartDATA worksheet which contains the Table 17.4 hotel
complaint data on page 729 Select the cell rangeB1:E51
and:
1. SelectInsert Scatterand select the fourth choice
from theScattergallery (Scatter with Straight Lines
And Markers.
2. Relocate the chart) to a chart sheet and adjust the chart
formatting by using the instructions in Appendix Sec-tion F.4 on page 815.
At this point, a recognizable chart begins to take shape, but
the control limit and center lines are improperly formatted
and are not properly labeled. To correct these formatting er-rors, use the three sets of instructions given in the Section
EG172In-Depth Excelinstructions.
EG17.5 CONTROL CHARTS for the RANGE
and the MEAN
TheRChart and the Chart
PHStat2UseR and XBar Chartsto create Rand
charts and supporting worksheets that compute the control
X
X
Chapter 17 Excel Guide 757
limits and plot points. For example, to create the Figure
17.6Rchart and the Figure 17.7 chart for the Table 17.5
luggage delivery times (see pages 734 and 735),Open to
the DATA worksheetof theHotel2 workbook. Because
the PHStat2 procedure requires column cell ranges that
contain either means or ranges, first add two columns that
compute the mean and ranges on this worksheet. Enter the
column headingMeanin cellG1and the headingRange
in cellH1. Enter the formula =AVERAGE (B2:F2) in cell
G2and the formula=MAX (B2:F2) - MIN (B2:F2) in cell
H2Select the cell range G2:H2and copy the range down
through row 29.
With the two columns created, selectPHStat Con-trol Charts R and XBar Charts. In the procedure 's dia-log box (shown below):
1. Enter5as the Subgroup/Sample Size.
2. EnterH1: H29as the Subgroup Ranges Cell Range.
3. CheckFirst cell contains label.
4. ClickR and XBar Charts. Enter G1:G29as the
Subgroup Means Cell Rangeand checkFirst cell
contains label.
5. Enter a Titleand click OK.
X
The procedure creates the two charts on separate chart
sheets and two supporting worksheets: one that computes
the control limits and one that computes the values to be
plotted. For more information about these two worksheets,
read the followingIn-Depth Excelsection
trang 757
Computing control limits and plotting points for other
problems requires changes to thecChartDATA worksheet
of thec Chart workbook. First, paste the time period and
number of nonconformances data into columns A and B of
the cChartDATA worksheet. If there are more than 50 time
periods, select cell rangeC51:E51and copy the range
down through all the rows. If there are fewer than 50 time
periods,Delete the extra rows from the bottom up, starting
with row 51.
Use the cChartDATA worksheet as the basis for creat-ing acchart. For example, to create the Figure 17.5cchart
for the hotel complaint data on page 729, open to the
cChartDATA worksheet which contains the Table 17.4 hotel
complaint data on page 729 Select the cell rangeB1:E51
and:
1SelectInsert Scatterand select the fourth choice
from theScattergallery (Scatter with Straight Lines
and Markers).
2. Relocate the chart to a chart sheet and adjust the chart
formatting by using the instructions in Appendix Sec-tion F.4 on page 815
At this point, a recognizable chart begins to take shape, but
the control limit and center lines are improperly formatted
And are not properly labeled. To correct these formatting er-rors, use the three sets of instructions given in the Section
EG17.2In-Depth Excelinstructions.
EG17.5 CONTROL CHARTS for the RANGE
and the MEAN
TheRChart and the Chart
PHStat2UseR and XBar Chartsto create Rand
charts and supporting worksheets that compute the control
X
X
Chapter 17 Excel Guide 757
limits and plot points. For example,To create the Figure
17.6Rchart and the Figure 17.7 chart for the Table 17.5
luggage delivery times (see pages 734 and 735), open to
the DATA worksheetof theHotel2 workbook. Because
the PHStat2 procedure requires column cell ranges that
contain either means or ranges, first add two columns that
compute the mean and ranges on this worksheet. Enter the
Column headingMeanin cellG1and the headingRange
in cellH1. Enter the formula =AVERAGE (B2:F2) in cell
G2and the formula=MAX (B2:F2) - M