I am trying to create an Rchart via Excel I have calculated

I am trying to create an R-chart via Excel. I have calculated the x-bar,x-double bar, R, R-bar, and stdandard deviation. I cannot figure out how to calculate the upper control limit of my R-chart. I have 400 points of data (10 measurements at 40 time intervals). I\'ve included the raw data from my problem.

	Observation
Day/Hour	1	2	3	4	5	6	7	8	9	10
1.1	40.94	40.21	39.89	40.25	40.64	40.37	41.97	42.10	41.90	40.82
1.2	42.32	40.55	40.77	42.30	41.25	40.63	41.21	39.96	40.68	41.34
1.3	39.56	42.53	41.30	39.90	41.40	42.46	42.91	41.55	41.19	41.50
1.4	39.89	40.20	41.33	39.31	39.46	40.31	41.56	41.16	41.37	41.57
1.5	40.83	40.36	39.03	40.59	41.54	42.52	39.61	39.98	40.03	41.67
1.6	39.41	41.68	40.85	41.69	40.10	42.25	40.68	41.13	38.51	41.91
1.7	41.29	41.13	42.20	39.58	41.33	41.90	41.67	39.82	41.26	41.17
1.8	40.89	39.90	41.22	39.67	41.18	39.69	41.05	42.97	42.17	39.23
2.1	40.94	41.94	38.98	41.59	42.51	41.58	39.76	41.56	38.92	41.30
2.2	39.74	40.50	39.45	42.01	41.97	41.40	41.96	42.17	40.80	41.24
2.3	39.83	40.57	41.40	39.32	41.12	40.13	38.13	41.95	40.67	40.65
2.4	40.97	40.56	42.27	42.05	41.18	41.92	41.84	39.25	39.78	40.86
2.5	40.46	43.17	42.43	41.72	39.99	40.98	41.24	41.39	41.52	42.17
2.6	42.91	41.86	40.84	40.71	40.23	43.46	40.81	40.46	40.11	42.38
2.7	40.72	42.11	41.52	40.02	41.55	40.11	40.86	40.63	40.02	41.68
2.8	41.67	40.77	39.42	42.18	39.65	39.79	42.37	38.84	41.05	41.30
3.1	39.98	42.91	40.74	41.98	40.32	41.05	41.62	40.33	40.85	40.76
3.2	41.84	41.70	40.84	40.38	41.08	42.07	42.72	38.77	41.13	40.74
3.3	42.69	42.21	40.19	42.27	41.06	42.33	39.90	42.11	40.10	40.30
3.4	41.45	42.22	41.46	38.82	41.67	39.69	41.72	42.53	43.27	40.91
3.5	40.35	40.10	41.65	39.77	43.18	41.22	40.29	39.89	40.28	41.31
3.6	41.28	41.43	43.75	41.74	40.61	42.02	39.34	40.14	41.12	39.20
3.7	42.23	40.52	41.72	39.59	40.34	39.37	41.44	43.49	40.81	39.83
3.8	40.53	42.31	42.71	42.86	43.17	41.42	42.58	40.18	40.87	40.45
4.1	41.48	42.03	40.80	40.81	39.67	40.53	42.05	41.27	41.40	41.98
4.2	39.82	41.76	41.47	40.55	39.98	41.98	41.58	39.13	41.73	39.82
4.3	40.18	41.21	42.63	42.51	39.99	39.89	40.31	41.09	39.89	40.15
4.4	39.83	40.56	40.07	41.25	40.54	39.03	40.25	41.04	42.14	40.65
4.5	41.87	41.87	42.04	41.18	41.27	40.26	40.89	40.70	42.37	40.07
4.6	42.29	40.77	40.31	40.51	40.90	39.25	40.45	41.67	40.59	40.83
4.7	43.23	42.84	40.14	41.49	40.51	39.51	42.32	41.96	41.38	40.56
4.8	42.14	40.46	41.44	40.91	41.90	41.00	41.14	41.81	42.43	41.11
5.1	41.44	41.48	41.71	41.75	39.67	39.21	41.26	39.30	40.60	42.03
5.2	41.41	41.01	41.97	40.35	40.16	39.45	40.55	41.16	40.29	42.49
5.3	40.86	40.91	38.89	40.20	41.76	41.11	41.67	40.31	41.54	41.35
5.4	40.17	41.91	41.51	40.45	43.01	40.18	40.67	41.79	39.86	40.68
5.5	41.26	40.45	40.73	40.98	41.07	42.18	43.42	41.66	40.26	40.62
5.6	41.33	37.68	40.70	40.88	40.56	40.90	41.64	40.98	39.67	39.23
5.7	40.66	41.28	40.09	41.57	40.83	39.85	40.85	40.31	42.17	40.35
5.8	41.19	41.37	39.55	40.97	42.22	41.03	42.42	41.26	40.50	40.35

Solution

R chart is a very simple kind of chart to generate. Here, we need to know Range of each sample. Xbar, Xdouble are not required to be calculated at all. Let\'s see, how R chart is generated.

Step 1: Calculate range for each sample. Range = Maximum value - Minimum value

Here we go -

Step 2: Calculate Rbar. It is calculated by taking average of range of each sample

Rbar = (2.21 + 2.36 + 3.35 +...... + 2.32+2.87) / 40 = 3.114

Step3: Pick up chartfactors D3 and D4 from table of constant. This D3 and D4 are factors that are used to calculate LCL and UCL. Value of D3 and D4 depends on sample size in each sub-group. In this case, each subgroup has 10 number of measurements.

Let\'s see the table of constant below.

Subgroup

Size (n)

A₂

D₃

D₄

d₂

2

1.880

0

3.267

1.128

3

1.023

0

2.574

1.693

4

0.729

0

2.282

2.059

5

0.577

0

2.114

2.326

6

0.483

0

2.004

2.534

7

0.419

0.076

1.924

2.704

8

0.373

0.136

1.864

2.847

9

0.337

0.184

1.816

2.970

10

0.308

0.223

1.777

3.078

11

0.285

0.256

1.774

3.173

12

0.266

0.284

1.716

3.258

13

0.249

0.308

1.692

3.336

14

0.235

0.329

1.671

3.407

15

0.223

0.348

1.652

3.472

16

0.212

0.364

1.636

3.532

17

0.203

0.379

1.621

3.588

18

0.194

0.392

1.608

3.640

19

0.187

0.404

1.596

3.689

20

0.180

0.414

1.586

3.735

21

0.173

0.425

1.575

3.778

22

0.167

0.434

1.566

3.819

23

0.162

0.443

1.557

3.858

24

0.157

0.452

1.548

3.895

25

0.153

0.459

1.541

3.931

In our case D3= 0.223, D4= 1.777

Step 4: Calculate UCL and LCL

UCL = D4*Rbar = 1.777 * 3.114 = 5.53

LCL = D3*Rbar = 0.69

You can generate Rchart based on these limits. In case you want to plot these values, you\'ll get a chart as shown below.

Day/Hour	Range
1.1	2.21
1.2	2.36
1.3	3.35
1.4	2.26
1.5	3.49
1.6	3.74
1.7	2.62
1.8	3.74
2.1	3.59
2.2	2.72
2.3	3.82
2.4	3.02
2.5	3.18
2.6	3.35
2.7	2.09
2.8	3.53
3.1	2.93
3.2	3.95
3.3	2.79
3.4	4.45
3.5	3.41
3.6	4.55
3.7	4.12
3.8	2.99
4.1	2.38
4.2	2.85
4.3	2.74
4.4	3.11
4.5	2.3
4.6	3.04
4.7	3.72
4.8	1.97
5.1	2.82
5.2	3.04
5.3	2.87
5.4	3.15
5.5	3.16
5.6	3.96
5.7	2.32
5.8	2.87