Skip to content

Home / Statistical Tools / Analysis Tools / Correlation and Covariance / How-To / GroupBy

GroupBy

Use GroupBy to produce a separate correlation matrix for each group in your data, so each site, batch or operator is measured on its own rows rather than blended with the others.

This example is worth running once even if you already know how GroupBy works, because the grouped answer and the ungrouped answer come out with opposite signs.

Goal

Measure how soak time relates to tensile strength at two sites, one matrix per site.

Sample Data

Six parts from each of two sites: soak time in minutes and the tensile strength that resulted. Press Copy for Excel, then paste into a blank worksheet.

Site Soak Time Strength
Site 1 20 81.2
Site 1 21 81.9
Site 1 22 82.4
Site 1 23 83.1
Site 1 24 83.6
Site 1 25 84.2
Site 2 31 74.1
Site 2 32 74.8
Site 2 33 75.2
Site 2 34 75.9
Site 2 35 76.5
Site 2 36 77.0

Site is the grouping column. Soak Time and Strength are the two measurement columns.

Notice the shape of the data before you chart it. Site 1 soaks for 20 to 25 minutes and reaches 81 to 84; Site 2 soaks for 31 to 36 minutes and reaches 74 to 77. The two sites sit in opposite corners of the plot, and inside each one, longer soaks go with higher strength.

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. Headers land in row 1 and the twelve rows in rows 2 through 13.

  2. Launch the analysis

    From the Excel ribbon, select QXL Stat Tools → Analysis Tools → Correlation and Covariance → Pearson Correlation.

  3. Select your data

    Select cells A1:C13, the header row plus all twelve data rows.

  4. Configure the analysis

    In the Correlation dialog:

    • Select the GroupBy radio button instead of Excel
    • Data Columns: Soak Time and Strength both checked, Site unchecked
    • GroupBy: move Site 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 correlation matrices, one per site, each built from that site's six rows.

Site 1 Site 2
Sample size 6 6
Pearson correlation +0.999 +0.998

Both sites show the same relationship, and it is about as strong as a correlation gets. Longer soak, higher strength, at both sites.

Run the same data without GroupBy and the sign flips. Set the calculation type back to Excel and finish again: one matrix, twelve rows, and a Pearson correlation of -0.839. Nothing about the measurements changed. The two sites sit at different soak times and different strengths, so a line drawn through all twelve rows runs downhill even though the line inside each site runs uphill. The grouped matrices are the ones computed from comparable rows.

The other two analysis types behave the same way

Run the same data as Spearman Correlation and each site returns +1.000, since strength increases with every step up in soak time at both sites, while the twelve rows together return -0.510. As Covariance each site returns +2.08 and +2.03, and the twelve rows together return -19.58. The reversal is a property of the data and the grouping, not of which coefficient you pick.

How GroupBy works here

Quantum XL creates a separate matrix for every distinct value in the grouping column, each with its own coefficients, its own p-values and its own sample size. A group needs at least two rows for a correlation and enough rows for the p-value to mean anything; with six rows per group here, both matrices are complete.

See Also