Home / Statistical Tools / Control Charts / CUSUM / How-To with GroupBy
How-To with GroupBy¶
This walkthrough charts the same measurement from two coating heads in one run. GroupBy divides the rows by the value of a grouping column and charts each group separately, so each head gets its own center, its own sigma estimate, and its own accumulation started from scratch.
On this chart the grouping reaches further than it does on most. Allowance (k): and Decision interval (h): are in standard deviations, not in microns, and you type one value of each for the whole run. Because each group estimates its own standard deviation, that single pair of numbers becomes a different allowance and a different decision interval on each chart. The two heads here end up with limits at 4.52 and 2.31, from the same h of 4.0.
If you have not built a plain CUSUM chart yet, start with the CUSUM How-To and come back.
The data¶
Twenty-four consecutive hours from each of two coating heads, one thickness reading per hour, in microns. Head 2 runs at a lower level than Head 1 and holds it more tightly. From its reading 18 onward Head 2 runs thicker and stays there.
| Head | Coating Thickness |
|---|---|
| Head 1 | 23.47 |
| Head 1 | 24.27 |
| Head 1 | 25.40 |
| Head 1 | 24.12 |
| Head 1 | 23.54 |
| Head 1 | 22.94 |
| Head 1 | 25.65 |
| Head 1 | 24.58 |
| Head 1 | 24.04 |
| Head 1 | 23.64 |
| Head 1 | 21.88 |
| Head 1 | 24.93 |
| Head 1 | 23.72 |
| Head 1 | 24.77 |
| Head 1 | 26.60 |
| Head 1 | 24.41 |
| Head 1 | 24.67 |
| Head 1 | 25.98 |
| Head 1 | 24.61 |
| Head 1 | 25.01 |
| Head 1 | 23.47 |
| Head 1 | 23.77 |
| Head 1 | 22.92 |
| Head 1 | 26.04 |
| Head 2 | 21.18 |
| Head 2 | 22.80 |
| Head 2 | 22.19 |
| Head 2 | 21.71 |
| Head 2 | 22.49 |
| Head 2 | 21.49 |
| Head 2 | 23.19 |
| Head 2 | 22.33 |
| Head 2 | 21.98 |
| Head 2 | 22.43 |
| Head 2 | 21.41 |
| Head 2 | 22.31 |
| Head 2 | 22.54 |
| Head 2 | 22.54 |
| Head 2 | 22.30 |
| Head 2 | 21.48 |
| Head 2 | 22.19 |
| Head 2 | 22.43 |
| Head 2 | 23.24 |
| Head 2 | 22.82 |
| Head 2 | 22.74 |
| Head 2 | 23.54 |
| Head 2 | 24.17 |
| Head 2 | 23.92 |
The Head column is the grouping column. It is not a measurement, and you do not check it in the measurement list.
The drift in Head 2 is 1.10 microns. Twelve of Head 1's twenty-three hour-to-hour moves are larger than that on their own, the biggest being 3.12, and none of them means anything. Keep that number in mind for the last section.
Neither group may contain a gap
A CUSUM chart refuses to run if any point has no usable value. Under GroupBy that applies to every group, so one blank cell anywhere in the measurement column stops the whole run, not just the chart for the group it sat in. Delete the row rather than blanking the cell.
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 forty-eight readings in rows 2 through 49.
-
Start the chart
From the Excel ribbon: QXL Stat Tools Tab > Control Charts > Time Weighted Charts > CUSUM.
-
Give it the cells
In the Data Selection window set Selected Range: to A1:B49. Leave Data in Columns selected and First Row/Column is Header checked. Press Next >.
-
Check the measurement column and leave the subgroup size at 1
The CUSUM Chart dialog opens on the Data tab. Check Coating Thickness. Leave Head unchecked: a column can only do one job at a time, and Head is about to become the grouping column.
Leave the Subgroups group on Constant size: with
n = 1, which is one reading per point. -
Switch the calculation type to GroupBy
Change the calculation type from Excel to GroupBy. The GroupBy region appears below it.
-
Move Head into the GroupBy order
Select Head 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.
-
Leave the CUSUM Parameters group alone
On the Limits & Estimation tab, leave Allowance (k): at 0.5, Decision interval (h): at 4.0, Target (optional): blank, and both Use fast initial response (FIR) and Reset accumulation after a signal unchecked.
There is one CUSUM Parameters group, not one per group of data. Whatever you set here is used on every chart the run produces.
-
Finish
Press Finish.
What you should see¶
Two worksheets, not one. They are named CUSUM - Head 1 and CUSUM - Head 2, each holding one chart of twenty-four points with a C+ series, a C- series, a center line at zero, and two flat limits.
Every number below belongs to one head alone. Nothing on either sheet was computed from the other head's rows.
| Worksheet | Center of the accumulation | Average moving range | Sigma (within) | Allowance | Decision interval |
|---|---|---|---|---|---|
| CUSUM - Head 1 | 24.35 | 1.28 | 1.13 | 0.57 | 4.52 |
| CUSUM - Head 2 | 22.48 | 0.65 | 0.58 | 0.29 | 2.31 |
You typed one allowance and one decision interval and got two of each. k of 0.5 and h of 4.0 are multiples of a standard deviation, and the two heads estimated different standard deviations, so Head 1's limits are drawn at plus and minus 4.52 while Head 2's are at plus and minus 2.31. Head 1's interval is 1.96 times Head 2's, which is exactly the ratio of their two sigma estimates.
Head 1 is clean. Neither of its sums signals, and neither gets close: C+ peaks at 1.99 and C- at 2.05, against an interval of 4.52.
Head 2 has two points out of control, 23 and 24. Its C+ climbs as the drift accumulates and crosses on point 23:
| Plotted point | Reading | C+ | Signalled? |
|---|---|---|---|
| 18 | 22.43 | 0.00 | |
| 19 | 23.24 | 0.48 | |
| 20 | 22.82 | 0.53 | |
| 21 | 22.74 | 0.51 | |
| 22 | 23.54 | 1.28 | |
| 23 | 24.17 | 2.69 | yes |
| 24 | 23.92 | 3.84 | yes |
Point 23 reaches 2.69 against the decision interval of 2.31. Both points signal rather than just the first, because Reset accumulation after a signal is off by default and a sum that has crossed stays across.
The smaller drift is the one that signals
Head 2's drift of 1.10 microns is 1.90 times its own sigma. The same 1.10 in Head 1 would be only 0.97 times that head's sigma, and two separate things then work against a signal there: Head 1's allowance absorbs 0.57 of every deviation where Head 2's absorbs only 0.29, and Head 1's limit sits at 4.52 where Head 2's sits at 2.31. A bigger bite taken out of each deviation, and twice as far to travel.
Check it rather than take it on trust. Add 1.10 to Head 1's last seven readings and run that column on its own: not one point signals. Its C+ reaches 3.12 against a decision interval of 4.69, still well short.
So the same change in microns signals on one head and not on the other. Each chart judges a drift against the variation of the head it was drawn from, which is what grouping bought you.
Each accumulation starts from scratch
Both sums begin at zero at the first point of each group. Head 2's accumulation carries nothing over from the end of Head 1, and the row order inside each group is the order you supplied. Grouping separates the rows before any deviation is accumulated.
Things to try next¶
- Run it without GroupBy. Set the calculation type back to Excel and finish again. You get one chart of forty-eight points with a single center at 23.41, which sits above every Head 2 reading and below most of Head 1's, and a single sigma of 0.93 that describes neither head. Fourteen points signal on C+ and twenty-one on C-, and the sums reach 11.78 and 14.15 against an interval of 3.71. The chart is not reporting a process problem. It is reporting that it was given two processes and told to accumulate their deviations from a common center.
- Turn on Reset accumulation after a signal. Run it again with that box checked and Head 2 signals on point 23 only, its sum restarting at 1.16 on point 24. Head 1 is unaffected, because it never signalled.
- Enter a target, and see how completely it changes the question. Put 24.0 in Target (optional): and both heads are measured against 24.0 rather than against their own means. Head 2 averages 22.48, so it is genuinely 1.5 microns below target for its whole run, and its lower sum accumulates that gap from the first point: it signals on every one of its twenty-four points and reaches 30.31 against an interval of 2.31. Head 1, averaging 24.35, signals on nothing. Neither chart has changed its data. With the target blank they were asked whether each head had moved away from its own average, and Head 2's drift was the answer. With a target they are asked whether each head is on 24.0, and Head 2's offset drowns out its drift entirely. One target applies to every group, so use one when the groups are all supposed to hit the same number, not when each has a level of its own.
- Group by two columns. Add a second grouping column and it becomes
2.in GroupBy Order, subdividing what1.produced. - Compare with a split instead. Split Control Limits also restarts the accumulation and re-estimates the center and the interval, 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.