Copyright 2000: James R. Evans. For use exclusively with Managing for Quality and Performance Excellence, 11th 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.
3. Enter the number of samples on which to base control limits in cell W3.
This number must be less than or equal to the number of samples specified in cell E3.
Note that the grand average and average range in cells C27 and C28 are calculated based on these samples only.
For example, to illustrate the effect of a shift in the mean of a controlled process, you might base control limits on the first 30 samples
and cause a mean shift starting at sample 31.
To illustrate the analysis for constructing a control chart by identifying out of control conditions and recalculating limits, you would base
X-bar and R-Chart Simulation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
26
27
28
29
30
31
32
33
34
35
36
37
49
50
51
52
53
54
55
56
57
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
7Upper specification 10
1Lower 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
Grand Average A2 D3 D4 d2 6.00
Average Range 0.42 0.08 1.92 2.7 0.89
Mean 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6.6 6.6 6.6 6.6 6.6 6.6 6.605 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6
Std. Dev. 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.889 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89 0.89
s Shift
DATA 12345678910 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
15.9 7.4 5.4 5.8 6 5.4 5.8 6.5 5.5 5.1 7.1 6.6 4 4.9 7.1 7 6.6 7.4 7 6 7.3 7.2 7.7 7.8 8.3 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
26 4.7 6.6 6.5 5.6 7.6 5.9 5.2 4.1 5.4 6.1 5 4.6 6.8 6.6 6.7 7.2 5.5 5.9 5.3 7.7 6.6 6.1 5.6 7 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
37.9 6.6 6.1 5 5.7 5.4 6.3 7.2 5 5.4 5.1 5.7 7.4 6.9 6.9 7.2 8.1 7.8 5.9 8.3 6.4 7 5.7 6.9 6.8 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
45.8 6.3 6.6 6.7 7 7.1 6.2 4.9 8.5 6.1 6.1 5.7 6.1 6.6 5.7 6.3 6.1 7.3 6.5 7 4.7 5.5 5.2 6 7.5 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
Range 2.7 2.7 1.7 1.8 1.6 2.6 2.7 2.3 4.4 2.7 2.1 3 3.4 2.2 1.7 1.3 3.7 2.9 1.1 3 3 2.8 2.5 2.2 2.7 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
LCLrange 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.191 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19 0.19
Center 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.512 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51 2.51
UCLrange 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.833 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83 4.83
DO NOT MODIFY THIS TABLE
nA2 D3 D4 d2 A3 B3 B4
2 1.88 0 3.27 1.13 2.66 0 3.27
Standard Deviation
Mean
Control Chart Factors
Sample size (2 – 10)
2.512
6.3645714
15
60%
6.6047432
Mixture
Mean trend down
Mean shift up
Mean trend up
X-bar and R-Chart Simulation
Number of decimal places
IMPORTANT! PRESS F9 TO UPDATE SPREADSHEET
Mean shift down
#VALUE!
Percent of shift from mean to control limit
Percent of trend from mean to control limit
Percent of shift from mean to control limit
Percent of trend from mean to control limit
#VALUE!
#VALUE!
3
3.5
4
7
7.5
8
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
0
4
5
6
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