Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Copyright 2000: James R. Evans. For use exclusively with The Management and Control of Quality, 5th Edition or higher.
This software may not be used for any commercial purpose, modified, or or otherwise distributed without written permission from the author.
This Excel template allows you to simulate control charts having a variety of out of control conditions.
The template has three primary worksheets: Data and calculations, x-bar chart, and R-chart. Click on the appropriate tabs.
Enter data ONLY in yellow-shaded cells. Do not change the formulas in any other cell.
1. Enter the number of samples, sample size, and number of decimal places in the range E3:E5.
You may generate up to 50 samples with sample sizes of 2 to 10, with a specified number of decimal places (1 or 2 recommended).
Larger number of decimal places will require changing the cell widths.
2. Enter a desired process capability index (Cp) and upper and lower specification limits in the range L3:L5
The mean and standard deviation of individual observations are calculated from these parameters.
The nominal mean is (USL+LSL)/2; sigma = (USL-LSL)/(6*Cp)
This also allows you to generate data for process capability analysis exercises with known characteristics.
X-bar and R-Chart Simulation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY
Number of samples (<= 50)
25
Process Capability (Cp)
1.5
Number of samples on which to base control limits
25
7
Upper specification
10
1
Lower specification
2
Sample at which shift begins
Sample at which trend begins
Percent of samples
Percent of shift from mean to control limit
Percent of trend from mean to control limit
Sigma change in mean
Calculated mean
Base calculation
Sample at which shift begins
Sample at which trend begins
Percent of shift from mean to control limit
Percent of trend from mean to control limit
Calculated mean
Base calculation
DO NOT MODIFY THIS TABLE
n
A2
D3
D4
d2
A3
B3
B4
2
1.88
0
3.27
1.13
2.66
0
3.27
Control Chart Factors
Sample size (2 - 10)
#VALUE!
#VALUE!
IMPORTANT! PRESS F9 TO UPDATE SPREADSHEET
Mean shift up Mean trend up
Mean shift down
X-bar and R-Chart Simulation
Number of decimal places
#VALUE!
Mixture
Mean trend down
#VALUE!
3
3.5
4
4.5
7.5
1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35 37 39 41 43 45 47 49
Sample number
X-bar Chart
Averages
Lower control limit
Upper control limit
Center line
4
4.5
1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35 37 39 41 43 45 47 49
Sample number
R-Chart Ranges
Lower control limit
Upper control limit
Center line
