Home / Statistical Tools / Analysis Tools / Confidence Interval / How-To / GroupBy
GroupBy¶
Use GroupBy to calculate a separate confidence interval for each group in your data, so each line, shift or supplier gets its own interval built only from its own rows.
Goal¶
Calculate a 95% confidence interval for the mean fill weight on each of two filling lines, and compare them.
Sample Data¶
Ten bottles weighed from each of two lines, in grams. Press Copy for Excel, then paste into a blank worksheet.
| Line | Weight |
|---|---|
| Line A | 152.3 |
| Line A | 151.8 |
| Line A | 152.6 |
| Line A | 151.4 |
| Line A | 152.1 |
| Line A | 152.9 |
| Line A | 151.6 |
| Line A | 152.4 |
| Line A | 152.0 |
| Line A | 151.9 |
| Line B | 151.2 |
| Line B | 154.6 |
| Line B | 149.8 |
| Line B | 153.1 |
| Line B | 150.4 |
| Line B | 155.2 |
| Line B | 148.9 |
| Line B | 153.8 |
| Line B | 150.1 |
| Line B | 152.9 |
Line is the grouping column. It is not a measurement and you do not check it in the data column list.
Steps¶
-
Put the data in Excel
Press Copy for Excel above the table, click cell A1 in a blank worksheet, and press Ctrl+V. Headers land in row 1 and the twenty weights in rows 2 through 21.
-
Launch the analysis
From the Excel ribbon, select QXL Stat Tools → Analysis Tools → Confidence Interval → CI for the Mean.
-
Select your data
Select cells A1:B21, the header row plus all twenty data rows.
-
Configure the analysis
In the Confidence Interval dialog:
- Select the GroupBy radio button instead of Excel
- Data Columns: Weight should be checked, Line unchecked
- Confidence Level: 0.95, the default
- GroupBy: move Line into the GroupBy Order list
Click Finish.
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.
Result¶
Two whisker charts and two statistics tables, one per line. Each interval is built only from that line's ten rows.
| Line A | Line B | |
|---|---|---|
| Sample size | 10 | 10 |
| Mean | 152.10 | 152.00 |
| Standard deviation | 0.459 | 2.199 |
| Standard error | 0.145 | 0.695 |
The two lines are hitting almost the same average. Line A's mean is 152.10 g and Line B's is 152.00 g, a tenth of a gram apart.
They are not filling with anything like the same consistency. Line B's standard deviation is 2.199 g against Line A's 0.459 g, nearly five times as large, and its standard error is nearly five times as large too, since both lines have ten observations. Line B's interval is therefore much the wider of the two, and the whisker chart shows it at a glance: two intervals centered on nearly the same value, one of them several times longer than the other.
Both intervals are centered on their own line's mean, and each one's width comes from that line's own standard error and the t multiplier for its own sample size. Nothing from the other line enters either calculation.
How GroupBy works here
Quantum XL creates a separate whisker chart and statistics table for every distinct value in the grouping column. Without GroupBy, the same twenty rows produce a single interval: the pooled mean is 152.05 with a standard deviation of 1.547, which describes neither line. It sits between the two lines' spreads and reports one number where there were two very different processes.