Skip to content

Home / Statistical Tools / Analysis Tools / Cpk/Histogram / How-To / GroupBy

GroupBy

Use GroupBy to run a separate capability analysis for each group in your data, such as each machine, shift, or production line, from one run.

Goal

Run a capability analysis on the diameter measurements of Machine A and Machine B separately, both using the same specification limits (LSL = 9.5, USL = 10.5).

Sample Data

Press Copy for Excel, then paste the table into a blank worksheet.

Diameter Machine
9.82 Machine A
10.05 Machine A
9.97 Machine A
10.12 Machine A
9.91 Machine A
10.25 Machine B
9.78 Machine B
10.35 Machine B
9.65 Machine B
10.18 Machine B

Each row represents one diameter measurement tagged with the machine that produced it. Machine A has 5 observations from 9.82 to 10.12, and Machine B has 5 observations from 9.65 to 10.35.

Steps

  1. Put the data in Excel

    Press Copy for Excel above the table, click cell A1 in a blank worksheet, and press Ctrl+V. The header row lands in row 1 and the 10 data rows in rows 2 through 11.

  2. Launch the analysis

    From the Excel ribbon, select QXL Stat Tools → Analysis Tools → Capability Analysis.

  3. Select your data

    In the Data Selection window, select cells A1:B11 (the header row plus all 10 data rows). Make sure Data in Columns and First Row/Column is Header are selected, then click Next >.

  4. Configure the analysis

    In the Process Capability (Cpk) and Histogram dialog, on the Data tab:

    • Select the GroupBy radio button (instead of Excel). The GroupBy section appears
    • Measurement Columns: "Diameter" is checked for you. "Machine" holds text, so it cannot be checked as a measurement column
    • GroupBy: select "Machine" in Available Items and click > to move it to the GroupBy Order list
    • Subgroups: leave Constant size: at n = 1
    • Specification Limits: in the Diameter row, type 9.5 under LSL and 10.5 under USL

    Click Finish to generate the charts.

How GroupBy Works

When you add a GroupBy column, Quantum XL runs the analysis separately for every unique value in that column and puts each group on its own worksheet. In this example, the "Machine" column has two unique values ("Machine A" and "Machine B"), so you get two worksheets, each with its own chart and capability statistics. The specification limits you enter apply to every group.

Result

Quantum XL creates two worksheets, one for each machine. Each worksheet's name starts with Capability - followed by the group, and each chart title names the group and the Diameter column. The statistics on each worksheet are calculated from that machine's 5 values only, including the within standard deviation from its own moving ranges:

Machine A: mean 9.974, overall standard deviation about 0.117, within standard deviation about 0.148. Cpk is about 1.06 and Ppk is about 1.35.

Machine B: mean 10.042, overall standard deviation about 0.308, within standard deviation about 0.503. Cpk is about 0.30 and Ppk is about 0.50.