# X-bar and R Control Chart Limits Worksheet

> Source: https://netray.co/tools/x-bar-r-control-chart-limits-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Type in subgroups of five readings and get subgroup means, ranges, control limits for both charts and a within-subgroup sigma estimate.

## Category

quality

## Industry

Manufacturing

## Audience

- Quality engineer
- Process engineer
- Machine operator lead

## Tool

**Kind:** worksheet

**Mode:** editable

**Columns:**

#### Subgroup

**Key:** subgroup

**Type:** text

#### X1

**Key:** x1

**Type:** number

**Format:** decimal

#### X2

**Key:** x2

**Type:** number

**Format:** decimal

#### X3

**Key:** x3

**Type:** number

**Format:** decimal

#### X4

**Key:** x4

**Type:** number

**Format:** decimal

#### X5

**Key:** x5

**Type:** number

**Format:** decimal

#### X-bar

**Key:** xbar

**Type:** number

**Expr:** (x1 + x2 + x3 + x4 + x5) / 5

**Format:** decimal

#### Range

**Key:** range

**Type:** number

**Expr:** max(max(max(x1, x2), max(x3, x4)), x5) - min(min(min(x1, x2), min(x3, x4)), x5)

**Format:** decimal

**Rows:**

**Subgroup:** S1 08:00

**X1:** 25.002

**X2:** 25.011

**X3:** 24.996

**X4:** 25.005

**X5:** 25.001

**Subgroup:** S2 08:30

**X1:** 25.008

**X2:** 24.998

**X3:** 25.004

**X4:** 25.012

**X5:** 25

**Subgroup:** S3 09:00

**X1:** 24.995

**X2:** 25.003

**X3:** 25.007

**X4:** 25.001

**X5:** 24.999

**Subgroup:** S4 09:30

**X1:** 25.006

**X2:** 25.01

**X3:** 25.002

**X4:** 24.997

**X5:** 25.004

**Subgroup:** S5 10:00

**X1:** 25.003

**X2:** 24.999

**X3:** 25.009

**X4:** 25.005

**X5:** 25.001

**Subgroup:** S6 10:30

**X1:** 25.012

**X2:** 25.006

**X3:** 25

**X4:** 25.008

**X5:** 25.003

**Subgroup:** S7 11:00

**X1:** 24.998

**X2:** 25.002

**X3:** 25.005

**X4:** 25

**X5:** 25.007

**Subgroup:** S8 11:30

**X1:** 25.004

**X2:** 25.009

**X3:** 25.001

**X4:** 24.996

**X5:** 25.006

**Totals:**

#### Grand average of X-bar

**Key:** xbar

**Agg:** avg

**Format:** decimal

#### Average range (R-bar)

**Key:** range

**Agg:** avg

**Format:** decimal

**Summary:**

#### Grand mean (X-double-bar)

**Key:** grand

**Expr:** avg_xbar

**Format:** decimal

#### R-bar

**Key:** rbar

**Expr:** avg_range

**Format:** decimal

#### X-bar chart UCL (A2 = 0.577)

**Key:** ucl_x

**Expr:** grand + 0.577 * rbar

**Format:** decimal

**Primary:** true

#### X-bar chart LCL

**Key:** lcl_x

**Expr:** grand - 0.577 * rbar

**Format:** decimal

**Primary:** true

#### R chart UCL (D4 = 2.114)

**Key:** ucl_r

**Expr:** 2.114 * rbar

**Format:** decimal

#### R chart LCL (D3 = 0)

**Key:** lcl_r

**Expr:** 0 * rbar

**Format:** decimal

#### Within sigma estimate (R-bar / 2.326)

**Key:** sigma

**Expr:** rbar / 2.326

**Format:** decimal

**Notes:** Limits are for subgroup size 5 (A2 0.577, D3 0, D4 2.114, d2 2.326). For other sizes use the control chart constants table and recompute. Use at least 20 to 25 subgroups before fixing limits, and replace the example rows with your own data.

## Content

**Intro:** The X-bar and R chart is the workhorse of statistical process control for measured characteristics like diameters, thickness or torque. This worksheet does the arithmetic so you can focus on what the points say about your process.

**Sections:**

#### How to use the worksheet

**Body:** Take small rational subgroups, for example five consecutive parts every half hour, and enter each as a row. The sheet computes the subgroup average and range, then the grand mean and R-bar, and from them the control limits for both charts. Replace the example rows with your readings and add rows as needed, aiming for at least 20 to 25 subgroups before treating the limits as stable. Export to Excel to plot the points against the lines.

**Bullets:**

- Keep readings in the same unit and decimal places.
- Subgroups should be collected close in time so variation inside is common-cause only.
- Review the R chart first, then the X-bar chart.

#### Formulas and constants

**Body:** The centerline of the X-bar chart is the grand mean. Its limits are the grand mean plus or minus A2 times R-bar. The R chart has centerline R-bar, with UCL equal to D4 times R-bar and LCL equal to D3 times R-bar. For subgroup size 5, A2 is 0.577, D4 is 2.114 and D3 is 0. The within-subgroup sigma is R-bar divided by d2, which is 2.326 for n of 5. This sigma feeds Cp and Cpk, whereas the overall standard deviation feeds Pp and Ppk.

#### Interpreting the chart

**Body:** Check the R chart first. If the range is out of control, the X-bar limits are unreliable because they depend on R-bar. Then look at the X-bar chart for points beyond the limits, runs of eight points on one side of the centerline, six points trending in one direction, or other Western Electric and Nelson patterns. Investigate each signal, record the assignable cause and recalculate limits only after removing points with confirmed special causes. Never draw spec limits on the X-bar chart as if they were control limits.

#### Common mistakes

**Body:** Recalculating limits every day makes the chart follow the process and hide shifts. Using a subgroup that spans several machines or shifts inflates R-bar and widens the limits. Using the wrong constants for the subgroup size gives wrong limits, so check the table. Finally, remember that control limits come from the process voice, while specification limits are the customer voice.

**Cta:** If your measurements are captured in an MES or ERP inspection module, we can help set up a private pipeline that feeds control charts and alerts without retyping data.

**Faq:**

#### What subgroup size should I use?

**Answer:** Three to five is typical for X-bar and R charts. Larger subgroups detect smaller shifts but cost more to sample. For subgroup sizes above 10, an X-bar and S chart is preferred. This worksheet is built for five readings per subgroup.

#### Why check the R chart before the X-bar chart?

**Answer:** The X-bar limits are calculated from R-bar. If the variation within subgroups is unstable, R-bar is not a meaningful number, so the X-bar limits cannot be trusted. Bring the R chart into control first.

#### How many subgroups do I need to set limits?

**Answer:** A common guideline is 20 to 25 subgroups of size 4 to 5, giving 100 or more readings. With fewer, the limits are provisional and should be reviewed as more data arrive.

#### What is the difference between control limits and spec limits?

**Answer:** Control limits describe what the process is doing, calculated from its own data. Specification limits come from the drawing or customer. A process can be in control but incapable, or capable but out of control.

## Search terms

x bar r chart calculator, x-bar and r chart excel template, control chart limits calculator, xbar r chart formula, spc control chart worksheet, ucl lcl calculator x bar, control chart template excel, variable control chart subgroup size 5

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json