Home / Statistical Tools / Control Charts / I-MR-R/S / How-To with GroupBy
How-To with GroupBy¶
This walkthrough charts the same measurement from two anodizing baths 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 bath, each with its own estimates and its own limits on all three panels.
On this chart that separation reaches further than on any other, because each group estimates two standard deviations of its own. The data here is built so the two baths agree almost exactly on one of them and differ by a factor of three on the other. Both baths are in control on their own charts. Charted together in one pooled chart, four points are marked.
If you have not built a plain I-MR-R/S chart yet, start with the I-MR-R/S How-To and come back.
The data¶
Ten batches from each of two anodizing baths. Four parts were measured from each batch and the coating thickness of each was recorded in microns, so each row is one batch in one bath and holds one subgroup of four observations.
| Bath | Part 1 | Part 2 | Part 3 | Part 4 |
|---|---|---|---|---|
| Bath A | 24.76 | 24.46 | 24.96 | 24.49 |
| Bath A | 24.49 | 24.80 | 24.92 | 25.35 |
| Bath A | 25.38 | 25.28 | 25.13 | 25.31 |
| Bath A | 24.94 | 25.20 | 24.83 | 24.91 |
| Bath A | 24.51 | 24.36 | 24.46 | 24.60 |
| Bath A | 24.94 | 25.16 | 25.24 | 25.24 |
| Bath A | 25.46 | 25.11 | 25.41 | 25.48 |
| Bath A | 24.93 | 25.22 | 25.21 | 25.11 |
| Bath A | 24.78 | 24.98 | 24.93 | 25.56 |
| Bath A | 24.71 | 24.94 | 25.05 | 24.75 |
| Bath B | 25.30 | 25.59 | 25.49 | 25.64 |
| Bath B | 25.27 | 25.20 | 25.33 | 25.28 |
| Bath B | 24.68 | 24.54 | 25.03 | 24.46 |
| Bath B | 25.50 | 25.11 | 25.32 | 25.58 |
| Bath B | 24.09 | 24.06 | 23.95 | 23.68 |
| Bath B | 24.05 | 24.03 | 23.72 | 23.50 |
| Bath B | 26.25 | 26.43 | 26.02 | 26.11 |
| Bath B | 25.76 | 25.54 | 25.92 | 25.46 |
| Bath B | 25.95 | 26.14 | 26.38 | 26.39 |
| Bath B | 25.21 | 25.33 | 24.77 | 24.93 |
The Bath column is the grouping column. It is not a measurement, and you do not check it in the measurement list.
Keep each bath's rows together and in order
The top two panels are built from the differences between consecutive batch means, so the row order inside a group is part of the data. GroupBy separates the rows before any moving range is formed, which is what stops a moving range from straddling the two baths, but the order within each bath is still the order you supply.
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 the twenty batches in rows 2 through 21.
-
Start the chart
From the Excel ribbon: QXL Stat Tools Tab > Control Charts > Variables > Charts in Subgroups > I-MR-R/S.
-
Give it the cells
In the Data Selection window set Selected Range: to A1:E21. Leave Data in Columns selected and First Row/Column is Header checked. Press Next >.
-
Check the four part columns only
On the Data tab, check Part 1 through Part 4 in the measurement list. Leave Bath unchecked. A column can only do one job at a time, and Bath 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 Bath into the GroupBy order
Select Bath 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 four observations rather than four separate charts.
-
Finish
Press Finish.
What you should see¶
Two worksheets, not one. They are named I-MR-RS Chart - Bath A and I-MR-RS Chart - Bath B, each holding three panels of ten points, with the first position of each Moving Range panel blank.
| Worksheet | Panel | Center line | Lower control limit | Upper control limit |
|---|---|---|---|---|
| I-MR-RS Chart - Bath A | Individuals Chart | 24.98 | 24.16 | 25.81 |
| Moving Range Chart | 0.31 | 0 | 1.01 | |
| Range Chart | 0.43 | 0 | 0.98 | |
| I-MR-RS Chart - Bath B | Individuals Chart | 25.17 | 22.90 | 27.45 |
| Moving Range Chart | 0.85 | 0 | 2.79 | |
| Range Chart | 0.43 | 0 | 0.99 |
Both baths are in control. Not one point is marked on any of the six panels.
The two Range panels are near twins. Bath A's center line is 0.43 with an upper limit of 0.98, Bath B's is 0.43 with an upper limit of 0.99. Both baths estimate a within-subgroup standard deviation of 0.21. Whatever is different about these two baths, it is not the spread among four parts from the same batch.
The two Individuals panels are nothing like each other. Bath A's band reaches 0.82 microns either side of its center line, Bath B's reaches 2.27, nearly three times as far. That difference comes entirely from the other estimate: the standard deviation of the batch means is 0.27 in Bath A and 0.76 in Bath B, and the between-subgroup standard deviations the summary tables report are 0.25 and 0.75. Bath B holds its parts as consistently as Bath A does inside a batch, and wanders three times as much from batch to batch.
That is the reading the two panels are for, and each bath had to be estimated from its own rows to get it. The three panels are all drawn from the ten batches of the bath on that sheet, and nothing about the other bath enters any of them.
Things to try next¶
-
Run it without GroupBy. Set the calculation type back to Excel and finish again. One worksheet, twenty points, one set of limits over both baths:
Panel Center line Lower control limit Upper control limit Individuals Chart 25.08 23.52 26.63 Moving Range Chart 0.59 0 1.91 Range Chart 0.43 0 0.99 The Range panel is unaffected: 0.43 and 0.99, the same as each bath's own. The Individuals panel is the problem. Its band lands between the two baths, 1.56 either side, which is too wide for Bath A and too narrow for Bath B, and four of the twenty points are marked: points 15, 16, 17 and 19, which are Bath B's batches 5, 6, 7 and 9. Not one of them is outside a control limit. All four sit beyond two sigma of the pooled center line, two low at 23.95 and 23.82 against a lower two-sigma line of 24.04, and two high at 26.20 and 26.21 against an upper one of 26.12, which is what the pattern tests reading the zone lines pick up. On Bath B's own chart those same four means sit between 1.3 and 1.8 of its own sigmas from its center line, comfortably inside. One point on the pooled Moving Range panel is out of control as well, at 2.38 against an upper limit of 1.91.
-
Look at the moving range where the baths meet. On that pooled chart, point 11 is the first batch of Bath B, and its moving range of 0.64 is the difference between Bath B's first batch mean and Bath A's last. It is a comparison between baths sitting in a series of comparisons within one bath, and it feeds the estimate that draws the band. GroupBy removes it, which is the mechanical reason the two sets of limits differ.
- Compare the summary tables. Each sheet reports both estimates. Reading the within-subgroup values against the between-subgroup values, side by side across the two baths, is the shortest description of what these two baths do differently.
- Compare with a split instead. Split Control Limits also gives separate stretches their own estimates and limits, but it divides one chart by position rather than by the value of a column. Use a split when one process changed over time, GroupBy when the rows describe separate things.
- Group by two columns. Add a second grouping column and it becomes
2.in GroupBy Order, subdividing what1.produced. Every combination of values that occurs becomes its own worksheet, and each one needs at least five subgroups of its own.