Skip to content

Age vs Thickness GLM Simulation

Jump to bottom
Jared Tanner edited this page Feb 10, 2026 · 1 revision

In-Class Activity: Age vs Thickness in Google Sheets / Excel (N = 100)

Learning goals

  1. Create a scatterplot (Age on x-axis, Thickness on y-axis) and visually compare groups.
  2. Add linear trendlines and interpret the slope as an estimate of association with age.
  3. Test whether the slope differs from 0 (statistical evidence of an age effect).
  4. Test whether groups differ after accounting for age (adjusted group “offset”).

Part A — Create a dataset with 100 participants (10–12 min)

1) Set up columns

Create these headers in row 1:

  • A: ID
  • B: Group (Control/Patient)
  • C: GroupCode (0/1)
  • D: Age
  • E: Thickness

2) Fill in ID, Group, GroupCode (exactly 100 people; 50/50 split)

ID

  • In A2, type 1
  • In A3, type 2
  • Highlight A2:A3, then drag down until A101 (you should end at ID = 100)

Group

  • In B2:B51, enter Control (copy/paste or drag-fill)
  • In B52:B101, enter Patient

GroupCode

  • In C2, enter:
    • Google Sheets / Excel:
      =IF(B2="Patient",1,0)
  • Copy down to C101

3) Generate Age and Thickness

Age (random integer 20–80)

  • In D2, enter:
    • Google Sheets: =RANDBETWEEN(20,80)
    • Excel: =RANDBETWEEN(20,80)
  • Copy down to D101

Thickness (age decline + patient offset + noise)

This model is designed so students can detect:

  • Thickness declines with age (negative slope)

  • Patient group is lower after adjusting for age (group offset)

  • In E2, enter:

=2.90 + (-0.004)*(D2-20) + (-0.10)*C2 + 0.07*NORM.S.INV(RAND())
  • Copy down to E101

4) Freeze the random data (critical)

Otherwise the data will change every time the sheet recalculates.

  • Copy D2:E101
  • Paste special → Values only
    • Google Sheets: Edit → Paste special → Values only
    • Excel: Home → Paste → Paste Values

Part B — Make the plot (5–8 min)

1) Create two series so points are colored by group

The cleanest approach is to plot Control and Patient as separate series.

Add helper columns (recommended)

Create two new headers:

  • F: ControlThickness
  • G: PatientThickness

In F2:

=IF($B2="Control",$E2,NA())

In G2:

=IF($B2="Patient",$E2,NA())

Copy F2:G2 down to row 101.

2) Insert scatterplot (Age vs Thickness)

Excel

  1. Select D1:D101 (Age)
  2. Hold Ctrl/Cmd and also select F1:G101 (the two thickness series)
  3. Insert → Scatter → Scatter with only markers
  4. Confirm you see two colors/series

Google Sheets

  1. Select D1:D101, then (Ctrl/Cmd-select) F1:G101
  2. Insert → Chart
  3. Chart type → Scatter chart
  4. Confirm you see two series

3) Add linear trendlines for each group

Turn on:

  • Trendline = Linear
  • Display equation
  • (Optional) Display R²

Excel

  • Click Control series → Add Trendline → Linear → Display Equation, Display R²
  • Repeat for Patient series

Google Sheets

  • Chart editor → Customize → Series
  • Choose a series → enable Trendline → Label: Use equation
  • Repeat for the other series

Part C — Question 1: Does slope differ from 0? (10–12 min)

Interpretation (concept)

A linear trendline equation looks like:

Thickness = intercept + slope × Age

  • If slope ≈ 0 → no age association
  • If slope clearly nonzero → age association (positive or negative)
  • The formal test asks whether slope is statistically different from 0

Option 1 (Google Sheets): Compute p-value using LINEST

Pooled (all participants): Thickness ~ Age

  1. Pick two adjacent empty cells (e.g., I2:J2) and enter:
=LINEST(E2:E101, D2:D101, TRUE, TRUE)
  1. Extract the slope standard error:
=INDEX(LINEST(E2:E101, D2:D101, TRUE, TRUE), 2, 1)
  1. Compute t-statistic (slope / SE). Example structure:
  • Put slope in I2 (from LINEST)
  • Put SE(slope) in I3 (from INDEX)
  • Then in I4:
=I2/I3
  1. Two-sided p-value (df = n − 2):
=T.DIST.2T(ABS(I4), COUNT(D2:D101)-2)

Deliverable for students: report slope, t, and p-value.

Option 2 (Excel): Regression output (cleanest if available)

If the Analysis ToolPak is enabled:

  1. Data → Data Analysis → Regression
  2. Y Range: Thickness (E2:E101)
  3. X Range: Age (D2:D101)
  4. Read the slope coefficient and its p-value in the output

Part D — Question 2: Are groups offset after accounting for age? (10–15 min)

This is an adjusted comparison:

  • Does Patient vs Control differ at the same age?

Step 1: Test whether slopes differ (interaction)

Model:

Thickness = b0 + b1·Age + b2·GroupCode + b3·(Age×GroupCode)

  1. Create an interaction column:
  • H1: AgeXGroup
  • H2:
=D2*C2

Copy down to H101.

  1. Run regression with predictors: Age (D), GroupCode (C), AgeXGroup (H)

Interpretation

  • If b3 (Age×GroupCode) is significant → slopes differ by group (different age effect)
  • If b3 is not significant → proceed with common-slope model

Step 2: Test adjusted group difference (offset) with common slope

Model:

Thickness = b0 + b1·Age + b2·GroupCode

Run regression with predictors:

  • Age
  • GroupCode

Interpretation

  • b2 estimates the adjusted group difference (Patient − Control)
    • If b2 is significant and negative → Patient is lower after accounting for age
    • If b2 is not significant → no evidence of adjusted group difference

Clone this wiki locally