# An Excel spread sheet template for control charts Excel 97 does not have a built in statistical analysis tool for control charts However we can create an Excel spreadsheet template an

Document Sample

```					             An Excel spread sheet template for control charts
Excel 97 does not have a built in statistical analysis tool for control charts. However, we can create an Excel
spreadsheet template and use the Insert Chart to create them almost automatically.
• Our example has 25 subgroups with 4 observations in each subgroup. We will reserve the ﬁrst four
columns, A, B, C, and D for entering the subgroup data, one subgroup for each row. Cells A1, B1, C1
and D1 will be reserved for labels.
• The ﬁrst thing we need to calculate is the subgroup range. In cell E1 enter the name Range. Click on
cell E2 and click on the formula bar. Enter the formula:

= MAX(A2:D2)-MIN(A2:D2)

• Click on cell E2. Go to Edit → Copy. Highlight cells E3 through E26. Go to Edit → Paste. Click on
any of the cells E3 to E26. What do you notice?
• Click on cell E27. Go to the formula bar and enter:

= AVERAGE(E2:E26)

this will calculate the value of the average range, R. This is used to calculate the control limits.
• In F1 enter LCLR, for Lower Control Limit for the R-chart. In F2 enter the formula:

= 0*\$E\$27

The dollar signs freeze the cell so when you copy and paste it into cells F3 through F26, the cell number
does not change but always refers to cell E27 that contains the average range.
• In G1 put R-bar, for the average Range. In G2 enter the formula:

= \$E\$27

Use Edit and Paste to enter this formula into cell G3-G26.
• In H1 enter UCLR, for the Upper Control Limit for the R-chart. In H2 enter the formula:

= 2.282*\$E\$27

The multiplier would change if you had subgroups of size 3 or 5. Use Edit and Paste to enter this
formula into cells H3-H26.
You can also set up columns for the subgroup means, centerline, upper and lower control limits for an X
chart in columns I (subgroup means), J (Lower Control Limit Xbar), K (centerline, X-bar) and L (Upper
Control Limit Xbar).
• As you enter subgroups into the rows of the spread sheet, Excel will calculate the range, average range,
subgroup means, overall average and control limits for both the R-chart and the X chart.
• Once the data are entered, highlight columns E, F, G and H.
• Go to Insert Chart. Choose a Line Chart. You want lines with markers displayed. Since you have
highlighted columns E, F, G and H. The data range should contain something like:

= Sheet1!\$E\$2:\$H\$26

Add a title (R Control Chart) and legends on the X (Subgroup) and Y (Range) axes. Ask for a new
sheet, this will create a separate chart.
• You can repeat the charting using columns I, J, K and L for the X chart

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 37 posted: 2/3/2011 language: English pages: 1
Description: Excel Spread Chart document sample
How are you planning on using Docstoc?