Home / Statistical Tools / Control Charts / Z-MR / How-To with GroupBy
How-To with GroupBy¶
This walkthrough charts the same three short-run parts made in two machining cells. GroupBy divides the rows by the value of a grouping column and charts each group separately, so you get one worksheet per cell.
On a Z-MR chart that separation reaches something no other chart has: the part means themselves. Each group gets its own mean per part, so a cell that runs consistently a few hundredths high is measured against its own average rather than against a figure blended with the other cell's. This data is built so that the two cells are clean on their own charts and the pooled chart is a mess, and the mess is worth seeing because it looks like a process problem when it is really a grouping problem.
If you have not built a plain Z-MR chart yet, start with the Z-MR How-To and come back.
The data¶
Twenty-four turned shafts, in millimetres: three part numbers, four of each, made in both of two cells. The rows are grouped by cell and, inside each cell, by part.
| Cell | Part | Diameter |
|---|---|---|
| Cell 1 | S-100 | 12.000 |
| Cell 1 | S-100 | 12.001 |
| Cell 1 | S-100 | 12.011 |
| Cell 1 | S-100 | 11.963 |
| Cell 1 | S-220 | 24.978 |
| Cell 1 | S-220 | 25.005 |
| Cell 1 | S-220 | 24.972 |
| Cell 1 | S-220 | 24.972 |
| Cell 1 | S-340 | 39.976 |
| Cell 1 | S-340 | 40.004 |
| Cell 1 | S-340 | 39.993 |
| Cell 1 | S-340 | 40.005 |
| Cell 2 | S-100 | 12.072 |
| Cell 2 | S-100 | 12.071 |
| Cell 2 | S-100 | 12.038 |
| Cell 2 | S-100 | 12.036 |
| Cell 2 | S-220 | 25.083 |
| Cell 2 | S-220 | 25.038 |
| Cell 2 | S-220 | 25.047 |
| Cell 2 | S-220 | 25.057 |
| Cell 2 | S-340 | 40.070 |
| Cell 2 | S-340 | 40.046 |
| Cell 2 | S-340 | 40.043 |
| Cell 2 | S-340 | 40.052 |
Cell is the grouping column and Part is the part column. Neither is a measurement, and neither is checked in the measurement list. This chart is the one place where three columns each do a different job.
Keep each cell's rows together, and each part's rows together inside them
Two different groupings are read off the row order here. GroupBy splits the rows by cell before anything is computed, and inside each group the part column is cut into runs of consecutive rows. Interleaving the cells row by row would still chart correctly once GroupBy has separated them, but interleaving the parts inside a cell would turn four runs of one part into four runs of one row each, and a run of one row has no moving range at all.
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-four measurements in rows 2 through 25.
-
Start the chart
From the Excel ribbon: QXL Stat Tools Tab > Control Charts > Short Run > Z-MR (Z Score with Moving Range).
-
Give it the cells
In the Data Selection window set Selected Range: to A1:C25. Leave Data in Columns selected and First Row/Column is Header checked. Press Next >.
-
Check the measurement column and set the part column
On the Data tab, check Diameter in the measurement list, and set Part Column to Part. Leave Cell unchecked and out of the part slot; it 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 Cell into the GroupBy order
Select Cell 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, named ZMR - Cell 1 and ZMR - Cell 2. Each holds two panels of twelve positions with three run dividers, and each Moving Range panel has three blank positions, one at the start of every run.
The lines are identical on both sheets, because on this chart they are always identical:
| Panel | Center line | Lower control limit | Upper control limit |
|---|---|---|---|
| Z Chart | 0 | -3 | +3 |
| Moving Range Chart | 1.128 | 0 | 3.686 |
What differs between the two sheets is everything the data decides:
| Cell 1 | Cell 2 | |
|---|---|---|
| Mean subtracted for S-100 | 11.994 | 12.054 |
| Mean subtracted for S-220 | 24.982 | 25.056 |
| Mean subtracted for S-340 | 39.995 | 40.053 |
| Estimated standard deviation | 0.0166 | 0.0159 |
Both cells are in control. Not one point is marked on any of the four panels. Cell 1's largest standardized value is 1.86 and Cell 2's is 1.68, both comfortably inside plus or minus 3.
The two cells do not machine to the same size. Cell 2 runs high on every one of the three parts, by 0.061 mm on S-100, 0.075 on S-220 and 0.058 on S-340. Their estimated standard deviations, on the other hand, are nearly identical: 0.0166 against 0.0159. The cells differ in where they sit, not in how much they scatter, and GroupBy is what let each chart say so without the other cell interfering.
Things to try next¶
-
Run it without GroupBy. Set the calculation type back to Excel and finish again. One worksheet, twenty-four positions, and now six run dividers instead of three, because stacking the cells makes every part appear twice. The Z panel's limits are still 0 and plus or minus 3, and the whole chart falls apart against them:
Value Mean subtracted for S-100 12.024 Mean subtracted for S-220 25.019 Mean subtracted for S-340 40.024 Estimated standard deviation 0.0175 Every part mean now lands between the two cells, so all twelve Cell 1 rows plot below the center line and all twelve Cell 2 rows above it, and twenty of the twenty-four points are marked. Two of them are outside the limits, row 4 at -3.49 and row 17 at +3.66; the other eighteen are caught by the pattern tests, which is what a chart looks like when half the points sit on one side of the center line and half on the other. Nothing about the machining changed between the two runs of the analysis.
-
Note what the dividers did not do. The pooled chart has six run dividers, and the counting tests still ran straight through them on the Z panel, because a part change does not restart them; only a blank point does. That is why a run of same-side points can be built out of rows belonging to two different parts.
- Group by part instead of by cell. Move Part into the GroupBy order rather than the part column and you get a chart per part, each with one part in it. It works, but it is the long way round: the part column already separates the parts inside one chart, which is the whole reason this chart exists.
- Compare with Part Overrides. If both cells are supposed to hit the same nominal, entering that nominal in Override Mean for each part on the pooled chart measures both cells against the target instead of against a blended average, and the cell offset then reads directly off the Z panel rather than being half absorbed into the mean.