04-10: Chi-Square & ANOVA Test Suite¶

Home | All Projects | Notes | Exercises | Quiz Hub

Work with categorical counts and with several group means: a goodness-of-fit test, a test of independence on a real survey, and a one-way ANOVA with post-hoc comparisons.

Chapters applied: 12-01 · 12-02 · 12-03

Difficulty: ⭐⭐⭐ Intermediate–Advanced

The Data¶

File	Contents	Test
`data/health_survey.csv`	420 respondents: `exercise` (Regularly/Sometimes/Never) × `health` (Excellent/Good/Poor)	Goodness-of-fit and independence
`data/fertilizer_yield.csv`	45 plots, 3 fertilizers, growth in cm	One-way ANOVA

What You Produce¶

A goodness-of-fit test: is the exercise variable uniformly distributed across its three levels?
A 3 × 3 contingency table with margins, expected counts, and every cell contribution
A test of independence, with Cramér's V and an identification of which cells drive the result
A one-way ANOVA table built by hand and checked against software
Tukey's HSD post-hoc comparisons, with a clear statement of which fertilizers differ
Assumption checks throughout: expected counts ≥ 5, homogeneity of variance, normality of residuals

Excel Route¶

Step 1 — Build the contingency table¶

Insert ▸ PivotTable on health_survey.csv: Rows = exercise, Columns = health, Values = Count of respondent. Copy the interior counts only (not the Grand Total row/column) to a clean block at B2:D4, then add margins with SUM.

Alternative without a pivot:

=COUNTIFS($B$2:$B$421, $A7, $C$2:$C$421, B$6)

Step 2 — Goodness-of-fit on `exercise`¶

' H0: the three exercise levels are equally likely
=SUM(E2:E4)/3                                ' expected = n/3 for each
=(E2-$H$1)^2/$H$1                            ' cell contribution, fill down
=SUM(contributions)                          ' chi-square
=COUNTA(A2:A4)-1                             ' df = k - 1 = 2
=CHISQ.INV.RT(0.05, 2)                       ' critical = 5.9915
=CHISQ.DIST.RT(chi2, 2)                      ' p-value
=CHISQ.TEST(observed_range, expected_range)  ' p-value in one step

Step 3 — Expected counts for independence¶

=$E2*B$5/$E$5           ' B8: row total x column total / grand total

Fill B8:D10. The mixed $ references make it fill correctly in both directions.

Check every expected count is ≥ 5 before going further:

=MIN(B8:D10)>=5                              ' must be TRUE

Step 4 — Test of independence¶

=(B2-B8)^2/B8                                ' B13: cell contribution
=SUM(B13:D15)                                ' chi-square statistic
=SUMPRODUCT((B2:D4-B8:D10)^2/B8:D10)         ' the same, in one formula
=(ROWS(B2:D4)-1)*(COLUMNS(B2:D4)-1)          ' df = (3-1)(3-1) = 4
=CHISQ.INV.RT(0.05, 4)                       ' critical = 9.4877
=CHISQ.TEST(B2:D4, B8:D10)                   ' p-value
=SQRT(chi2/($E$5*MIN(ROWS(B2:D4)-1,COLUMNS(B2:D4)-1)))    ' Cramér's V

Sort the contribution table descending to see which cells drive the result. The overall test says "something is associated"; the contributions say what.

Step 5 — Row percentages for interpretation¶

In the PivotTable: right-click a value ▸ Show Values As ▸ % of Row Total. This turns the counts into P(health | exercise) and makes the pattern readable at a glance.

Step 6 — One-way ANOVA¶

Reshape fertilizer_yield.csv into three columns (one per fertilizer) using FILTER or a sort, then:

' Data ▸ Data Analysis ▸ Anova: Single Factor
'   Input Range = A1:C16, Grouped By: Columns, tick Labels in first row
'   -> SUMMARY (count, sum, average, variance per group)
'   -> ANOVA table (SS, df, MS, F, P-value, F crit)

' Built by hand, to see where each number comes from:
=DEVSQ(A2:A16)+DEVSQ(B2:B16)+DEVSQ(C2:C16)      ' SSW  (within)
=DEVSQ(A2:C16)                                   ' SST  (total)
=$H$2-$H$1                                       ' SSB = SST - SSW
=$H$3/(3-1)                                      ' MSB
=$H$1/(45-3)                                     ' MSW
=$H$4/$H$5                                       ' F
=F.INV.RT(0.05, 2, 42)                           ' critical F
=F.DIST.RT($H$6, 2, 42)                          ' p-value
=$H$3/$H$2                                       ' eta squared

Check homogeneity of variance first — the ToolPak SUMMARY block gives you the three variances:

=MAX(variances)/MIN(variances)                   ' want < 4

Step 7 — Tukey's HSD by hand¶

Excel has no Tukey test, so compute the honest significant difference from the ANOVA output:

' q(0.05, k=3, df=42) = 3.44 from a studentized-range table
=3.44*SQRT($H$5/15)                              ' HSD
=ABS(AVERAGE(A2:A16)-AVERAGE(B2:B16))>$H$9       ' A vs B significant?
=ABS(AVERAGE(A2:A16)-AVERAGE(C2:C16))>$H$9       ' A vs C
=ABS(AVERAGE(B2:B16)-AVERAGE(C2:C16))>$H$9       ' B vs C

R Route¶

Rscript r/analysis.R

Runs all three tests with full diagnostics and writes categorical_plots.png. See r/analysis.R.

Python Route¶

python python/analysis.py

Same via scipy.stats + statsmodels. See python/analysis.py.

Checkpoints¶

Everything is computed from counts, never percentages
Every expected count is ≥ 5 (or the classes are combined and df recomputed)
df = (r−1)(c−1), not rc − 1
Cramér's V is reported alongside χ², because χ² grows with n
The cells driving the association are identified from the contribution table
The post-hoc test is run only because the ANOVA F was significant
Homogeneity of variance is checked before the pooled ANOVA is trusted

Extend It¶

Collapse health to two levels and redo the test as a 3 × 2. Does the conclusion survive?
Run Fisher's exact test on a 2 × 2 sub-table and compare with the chi-square
Add a test of homogeneity framing: what would change if the three exercise groups had been sampled separately?
Replace the ANOVA with Welch's ANOVA and Kruskal-Wallis and compare all three conclusions