Home / Statistical Tools / Control Charts / Xbar-R and Xbar-S / How-To with GroupBy
How-To with GroupBy¶
This walkthrough charts the same measurement from two machines in one run. GroupBy divides the rows by the value of a grouping column and charts each group separately, so you get one worksheet per machine, each with its own center line and its own control limits.
That last part is the reason to use it. Each group is estimated only from its own rows, so a machine running with more variation gets a wider band rather than being averaged in with the others. One of the two machines here has a problem and the other does not.
If you have not built a plain Xbar-R chart yet, start with the How-To and come back.
The data¶
Ten hours from each of two machines. Five fasteners were checked each hour and the seating torque of each was recorded in inch-pounds, so each row is one hour on one machine and holds one subgroup of five observations.
| Machine | Reading 1 | Reading 2 | Reading 3 | Reading 4 | Reading 5 |
|---|---|---|---|---|---|
| Machine A | 51.3 | 51.4 | 50.1 | 49.2 | 48.9 |
| Machine A | 50.0 | 49.0 | 48.6 | 50.2 | 50.1 |
| Machine A | 50.5 | 49.1 | 50.0 | 49.9 | 48.5 |
| Machine A | 50.5 | 50.3 | 52.4 | 50.2 | 49.9 |
| Machine A | 51.2 | 50.2 | 50.9 | 49.6 | 50.2 |
| Machine A | 51.0 | 50.7 | 50.1 | 48.9 | 50.4 |
| Machine A | 50.1 | 50.7 | 50.2 | 51.1 | 49.9 |
| Machine A | 50.2 | 50.7 | 48.9 | 49.6 | 49.5 |
| Machine A | 52.0 | 49.9 | 50.7 | 50.6 | 49.7 |
| Machine A | 48.4 | 51.0 | 49.6 | 50.7 | 48.7 |
| Machine B | 49.6 | 51.3 | 51.4 | 48.7 | 48.7 |
| Machine B | 50.0 | 50.7 | 50.2 | 50.3 | 49.0 |
| Machine B | 50.6 | 51.1 | 49.6 | 48.6 | 49.2 |
| Machine B | 50.8 | 48.3 | 49.9 | 49.0 | 49.9 |
| Machine B | 49.8 | 50.0 | 51.5 | 50.4 | 51.3 |
| Machine B | 49.9 | 49.5 | 50.4 | 47.2 | 50.0 |
| Machine B | 53.2 | 51.8 | 53.5 | 52.4 | 50.5 |
| Machine B | 49.8 | 49.0 | 49.5 | 49.8 | 51.3 |
| Machine B | 50.1 | 50.0 | 50.4 | 48.2 | 51.2 |
| Machine B | 48.9 | 50.4 | 48.9 | 49.0 | 49.6 |
The Machine column is the grouping column. It is not a measurement, and you do not check it in the measurement list.
Steps¶
-
Put the data in Excel
Press Copy for Excel above the table. In Excel, open a blank worksheet, click cell A1, and press Ctrl+V. Headers land in row 1 and data in rows 2 through 21.
-
Start the chart
From the Excel ribbon: QXL Stat Tools Tab > Control Charts > Variables > Charts in Subgroups > Xbar-R.
-
Give it the cells
In the Data Selection window set Selected Range: to A1:F21. Leave Data in Columns selected and First Row/Column is Header checked. Press Next >.
-
Check the five reading columns only
On the Data tab, check Reading 1 through Reading 5 in the measurement list. Leave Machine unchecked. A column can only do one job at a time, and Machine is about to become the grouping column.
-
Switch the calculation type to GroupBy
Change the calculation type from Excel to GroupBy. The GroupBy region appears below it.
-
Move Machine into the GroupBy order
Select Machine in Available Items and press
>. It moves into GroupBy Order as1.Do not switch back to Excel after this
Switching the calculation type back to Excel clears the GroupBy list without warning, and returning to GroupBy means building it again.
-
Tell it the subgroups run across the columns
In the Subgroups group, select Subgroups across columns, so each row is one subgroup of five observations rather than five separate charts.
-
Finish
Press Finish.
What you should see¶
Two worksheets, not one. They are named Xbar-R Chart - Machine A and Xbar-R Chart - Machine B, each holding one chart of ten points.
Each machine's limits are computed only from that machine's rows:
| Worksheet | Panel | Center line | Lower control limit | Upper control limit |
|---|---|---|---|---|
| Xbar-R Chart - Machine A | Xbar Chart | 50.11 | 48.94 | 51.28 |
| Range Chart | 2.02 | 0 | 4.27 | |
| Xbar-R Chart - Machine B | Xbar Chart | 50.09 | 48.70 | 51.48 |
| Range Chart | 2.41 | 0 | 5.10 |
Machine A is clean. No point on either of its panels is marked.
Machine B has one point out of control. Point 7 on its Xbar Chart plots at 52.28, above its upper control limit of 51.48, and is drawn in a different color. Its Range Chart is quiet, so the spread within an hour did not change on that machine either. The level did.
The two center lines are almost identical and the two bands are not. Both machines average close to 50.1, but Machine B's band is the wider of the two, because its own hour-to-hour readings vary more and the limits are built from that variation. Averaging both machines into one chart would have given Machine B the benefit of Machine A's tighter data.
Things to try next¶
- Run it without GroupBy. Set the calculation type back to Excel and finish again. You get one chart of twenty subgroups on a single worksheet, with one set of limits covering both machines, and the machine each point came from is no longer visible anywhere on the chart.
- Group by two columns. Add a second grouping column and it becomes
2.in GroupBy Order, subdividing what1.produced. Every combination of values that actually occurs becomes its own chart, so the sheet count multiplies. - Compare with a split instead. Split Control Limits also gives different phases their own limits, but on one chart and split by position rather than by the value of a column. Use a split for a change over time, GroupBy for separate things measured side by side.