Home / Statistical Tools / Control Charts / I-MR / How-To with GroupBy
How-To with GroupBy¶
This walkthrough charts the same measurement from two production lines 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 line, each with its own center line and its own control limits.
That independence is the point. On an individuals chart the limits come from how much consecutive points differ, so a line that jumps around more between readings gets a wider band rather than being averaged in with a steadier one.
If you have not built a plain I-MR chart yet, start with the I-MR How-To and come back.
The data¶
Thirteen consecutive batches from each of two filling lines. One bottle was weighed from each batch, in grams.
| Line | Fill Weight |
|---|---|
| Line A | 20.05 |
| Line A | 20.63 |
| Line A | 19.53 |
| Line A | 20.50 |
| Line A | 19.87 |
| Line A | 19.87 |
| Line A | 20.95 |
| Line A | 20.08 |
| Line A | 19.98 |
| Line A | 20.36 |
| Line A | 20.56 |
| Line A | 19.98 |
| Line A | 20.29 |
| Line B | 19.51 |
| Line B | 19.82 |
| Line B | 19.78 |
| Line B | 19.33 |
| Line B | 19.25 |
| Line B | 19.19 |
| Line B | 19.88 |
| Line B | 19.91 |
| Line B | 22.44 |
| Line B | 20.03 |
| Line B | 19.33 |
| Line B | 19.96 |
| Line B | 20.12 |
The Line column is the grouping column. It is not a measurement, and you do not check it in the measurement list.
Keep each line's rows together and in order
An individuals chart plots rows in the order it reads them, and its limits come from the difference between neighbours. Interleaving the two lines row by row would make every moving range a comparison between lines rather than within one. GroupBy separates the rows before any moving range is formed, so grouping is the fix, but the order inside each group 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-six readings in rows 2 through 27.
-
Start the chart
From the Excel ribbon: QXL Stat Tools Tab > Control Charts > Variables > Charts as Individuals > I-MR.
-
Give it the cells
In the Data Selection window set Selected Range: to A1:B27. Leave Data in Columns selected and First Row/Column is Header checked. Press Next >.
-
Check the measurement column only
On the Data tab, check Fill Weight. Leave Line unchecked. A column can only do one job at a time, and Line is about to become the grouping column.
Remember there is no Subgroups group on this dialog, so a second checked measurement column would produce a second chart rather than a subgroup.
-
Switch the calculation type to GroupBy
Change the calculation type from Excel to GroupBy. The GroupBy region appears below it.
-
Move Line into the GroupBy order
Select Line 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.
-
Finish
Press Finish.
What you should see¶
Two worksheets, not one. They are named I-MR Chart - Line A and I-MR Chart - Line B, each holding two panels of thirteen points, with the first position of each Moving Range panel blank.
| Worksheet | Panel | Center line | Lower control limit | Upper control limit |
|---|---|---|---|---|
| I-MR Chart - Line A | Individuals Chart | 20.20 | 18.70 | 21.71 |
| Moving Range Chart | 0.57 | 0 | 1.85 | |
| I-MR Chart - Line B | Individuals Chart | 19.89 | 18.10 | 21.68 |
| Moving Range Chart | 0.67 | 0 | 2.20 |
Line A is clean. No point on either of its panels is marked.
Line B has one wild batch, flagged three times in total. Its ninth reading plots at 22.44, above that line's upper control limit of 21.68. On its Moving Range panel, points 9 and 10 are both out of control, at 2.53 and 2.41 against an upper limit of 2.20, because one wild value disturbs the moving range going into it and the one coming out of it.
Line B's band is the wider of the two, and its center line is lower. Line A sits at 20.20 with limits 1.5 grams either side; Line B sits at 19.89 with limits about 1.8 either side. Both differences come only from Line B's own rows. Had the two lines been charted together, Line B's spike would have been judged against limits partly built from Line A's steadier data, and Line A would have been judged against limits inflated by Line B's spike.
Things to try next¶
- Run it without GroupBy. Set the calculation type back to Excel and finish again. You get one chart of twenty-six points, and the moving range at the boundary between the two lines is a comparison between lines rather than within one, which is exactly the artefact GroupBy exists to prevent.
- Compare with a split instead. Split Control Limits also gives each phase its own limits, but it divides one chart by position rather than by the value of a column. Use a split when a single stream 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.