Chapter 10: Comparing Many Groups: ANOVA

Rosa, Ridgeline's Metro regional manager, has heard the complaint before. "Customers in the mountain stores love us. My customers are tougher to please." The survey seems to agree. Metro customers gave an average satisfaction rating of 7.01 out of 10, Suburban customers 7.83 and Mountain customers 8.36.

Marcus Bell wants to know whether that pattern is real before he does anything about it. Do the three regions really differ in customer satisfaction, or could a gap like this come from which 600 customers happened to answer the survey? And if they differ, which regions differ from which?

In Chapter 9. Comparing Two Groups you compared two means. Now there are three. This chapter introduces the standard tool for comparing several means at once, and then a way to find out which of them differ.

10.1 Prepare the Data in Excel

There's a small obstacle first. The Survey Table records the store_id of each response but not the region. Region lives in the Stores Table. You need to look it up.

You could add a region column to the Survey Table itself. In this chapter you'll build the lookup on a separate sheet instead, for a reason that will matter when you use R: R for Excel sends the Survey Table to R exactly as it is, and the R script will look up the region from Stores on its own. (If you do add a region column to the Table, say so in your data description, so the AI doesn't add a second one.)

1. Add a new sheet and name it RegionSat.

2. In A1 type region. In A2 enter

   =XLOOKUP(Survey[store_id],Stores[store_id],Stores[region])

   Because Survey[store_id] is a whole column, XLOOKUP returns 600 answers, which spill down from A2 to A601. Each row shows the region of the matching survey response.

3. In B1 type satisfaction. In B2 enter =Survey[satisfaction]. This spills the 600 ratings beside their regions.

You can now refer to the two spilled columns as $A$2# and $B$2#. The # means "the whole spill range that starts here." Use the $ signs: a spill reference that shifts when you fill a formula down (to A3#, say) points at nothing and returns an error.

Next, build a summary table. In D1:G1 type the headings region, n, mean, SD. In D2:D4 type Metro, Mountain, Suburban. Then enter these formulas in row 2 and fill them down to row 4:

Column	Formula in row 2	Metro	Mountain	Suburban
E (n)	=COUNTIFS($A$2#,D2)	217	190	193
F (mean)	=AVERAGEIFS($B$2#,$A$2#,D2)	7.01	8.36	7.83
G (SD)	=STDEV.S(FILTER($B$2#,$A$2#=D2))	1.28	1.26	1.21

The means differ by more than a full point between Metro and Mountain. The SDs, about 1.2 to 1.3, show how much individual ratings vary inside each region. Keep both kinds of numbers in mind: the whole chapter is about comparing them.

A chart helps. Select A1:B601 and choose Insert > Insert Statistic Chart > Box and Whisker. Excel draws one box for each region. You'll see the Metro box sitting lower than the others, with a lot of overlap among all three.

10.2 Why Not Three t-Tests?

With three regions there are three pairs: Metro vs. Mountain, Metro vs. Suburban, Mountain vs. Suburban. You already know how to compare two groups, so why not run three t-tests?

The problem is false alarms. Each test at the 0.05 significance level has a 5% chance of a Type I error: declaring a difference when there isn't one. That's a risk you accept once. Run three tests and you accept it three times. If none of the regions truly differ, the chance that at least one of three independent tests falsely comes out significant is

1 − 0.95³ = 1 − 0.857 ≈ 0.14

In Excel: =1-0.95^3. That's a 14% chance of a false alarm, not 5%. With 5 groups there are 10 pairs, and the chance climbs to about 40%. Look at enough comparisons and something will look "significant" by luck alone.

The fix comes in two steps. First, ask one question about all the groups at once: do any of the means differ? That single test has a 5% false-alarm rate. Second, only if the answer is yes, find which means differ, using a method designed to keep the overall false-alarm rate at 5%. The first step is ANOVA. The second is Tukey's HSD.

10.3 How ANOVA Works

Analysis of variance, or ANOVA, tests whether several groups have the same population mean.

The hypotheses are:

• H₀: μ(Metro) = μ(Mountain) = μ(Suburban). All three regions have the same average satisfaction.
• Hₐ: at least one region's mean is different.

Notice what Hₐ doesn't say. It doesn't say all three differ, or which one is different. ANOVA answers only "are they all the same?"

10.3.1 The idea: two kinds of variation

The name sounds odd: a test about means called "analysis of variance." Here's why. ANOVA decides whether the means differ by comparing two kinds of spread.

• Between-group variation is how far apart the group means are from each other (and from the overall mean). If the regions really differ, their means will be spread out.
• Within-group variation is how much individual customers vary around their own region's mean. This is the background noise: customers in the same region give different ratings for all sorts of reasons.

Picture two situations, each with three groups whose means are 7, 8 and 8.5. In the first, every group's ratings are tightly bunched around its own mean. The groups barely overlap, and you'd be confident they differ. In the second, each group's ratings are spread from 3 to 10. The groups overlap almost completely, and a gap of one point between means could easily be luck. The means are identical in both pictures. What changed is the within-group noise.

So the question ANOVA asks is: is the spread between the group means large compared with the noise inside the groups? That ratio is the F statistic.

F = (between-group variation per degree of freedom) ÷ (within-group variation per degree of freedom)

If H₀ is true, both the top and the bottom estimate the same thing (the background noise), so F should be close to 1. If the groups really differ, the top grows and F gets much larger than 1.

10.3.2 Sums of squares, step by step

To turn the idea into numbers, ANOVA measures each kind of variation with a sum of squares, the same "add up squared distances" you used for the variance in Chapter 4. Describing Data.

Total sum of squares (SST). Take every rating's distance from the grand mean (7.702), square it and add them all up. This is all the variation in the data.

Between-group sum of squares (SSB). For each region, take the distance from its mean to the grand mean, square it, and multiply by the number of customers in the region. Then add the three results. In symbols, SSB = Σ nⱼ (meanⱼ − grand mean)², where j runs over the groups. A region with many customers counts more, because its mean represents more ratings.

Within-group sum of squares (SSW). Inside each region, take every rating's distance from that region's mean, square and add. Then add the three regions' totals. Excel's DEVSQ function does the "square and add the distances from the mean" part for you.

The three always fit together: SST = SSB + SSW. Every bit of variation in the ratings is either between regions or within them.

10.3.3 The worked example in Excel

Add two columns to the summary table on the RegionSat sheet. Type the headings between SS in H1 and within SS in I1. Put the grand mean in L2 first (see the next table), then enter these in row 2 and fill down to row 4:

Column	Formula in row 2	Metro	Mountain	Suburban	Total
H (between SS)	=E2*(F2-$L$2)^2	104.05	81.82	3.39	189.26
I (within SS)	=DEVSQ(FILTER($B$2#,$A$2#=D2))	355.98	297.66	280.69	934.34

Look at column H. Suburban's mean (7.83) is close to the grand mean, so it contributes almost nothing to the between-group variation. Metro and Mountain, each about two-thirds of a point from the grand mean on opposite sides, contribute almost all of it.

Now build the ANOVA calculation in columns K and L:

Cell	Label (column K)	Formula (column L)	Result
L2	grand mean	=AVERAGE(B2#)	7.702
L3	SSB	=SUM(H2:H4)	189.26
L4	SSW	=SUM(I2:I4)	934.34
L5	SST (check)	=DEVSQ(B2#)	1123.60
L6	df between	=COUNTA(D2:D4)-1	2
L7	df within	=SUM(E2:E4)-COUNTA(D2:D4)	597
L8	MS between	=L3/L6	94.63
L9	MS within	=L4/L7	1.565
L10	F	=L8/L9	60.46
L11	p-value	=F.DIST.RT(L10,L6,L7)	< 0.001
L12	F critical (0.05)	=F.INV.RT(0.05,L6,L7)	3.011
L13	eta squared	=L3/L5	0.168

Here's what each step means.

• Check the total. 189.26 + 934.34 = 1123.60, which matches DEVSQ of all 600 ratings. If your SSB and SSW don't add up to SST, a formula is wrong.
• Degrees of freedom. Between groups, df is the number of groups minus 1: three group means, but once you know the grand mean, only two are free to vary. Within groups, df is the number of observations minus the number of groups: 600 − 3 = 597.
• Mean squares. A sum of squares grows just because there are more things being added. Dividing each one by its degrees of freedom turns it into an average squared distance, called a mean square (MS). MS within, 1.565, is the pooled variance of ratings inside the regions. Its square root, about 1.25, is a typical within-region SD, which matches the SDs in column G.
• F. The between-group mean square is 60 times larger than the within-group mean square. If the regions had equal means, F would typically be around 1.
• p-value. F.DIST.RT gives the area in the right tail of the F distribution beyond 60.46, with 2 and 597 degrees of freedom. The result is about 1.2 × 10⁻²⁴, which Excel displays as 1.23E-24: a decimal point followed by 23 zeros before the first digit. Only the right tail matters: large F is the evidence against H₀, and F can never be negative.
• F critical. F.INV.RT(0.05,2,597) gives the F value that cuts off the top 5% of the distribution, 3.011. Any F above 3.011 is significant at the 0.05 level. 60.46 is far beyond it.

The conclusion: reject H₀. Average satisfaction is not the same in all three regions.

10.3.4 How much does region matter?

A tiny p-value says the regions differ. It doesn't say by how much. As in Chapter 9. Comparing Two Groups, you need an effect size. For ANOVA, the usual one is eta squared: the share of all the variation in the outcome that lies between the groups.

Here η² = 189.26 ÷ 1123.60 = 0.168. About 17% of the variation in satisfaction ratings lines up with region. The other 83% is variation among customers within the same region. Rough guidelines call 0.01 small, 0.06 medium and 0.14 or more large, so region's effect is large by those standards. Still, most of what makes one customer happier than another has nothing to do with region. That's worth telling Rosa: even in Metro, plenty of customers give 9s and 10s.

10.3.5 The assumptions

One-way ANOVA has the same kinds of assumptions as the two-sample t-test.

1. Independent observations, within and between groups. Each survey response is a different customer, chosen separately, so this is reasonable.
2. Roughly normal data in each group, or large groups. With about 200 customers per region, the central limit theorem takes care of this. The satisfaction ratings are whole numbers from 4 to 10, which isn't truly normal, but the group means behave well at these sample sizes.
3. Similar standard deviations in the groups. Classic ANOVA pools the within-group variation into one number (MS within), so it assumes every group has about the same spread. A common rule of thumb: the largest SD should be less than twice the smallest. Here the SDs are 1.28, 1.26 and 1.21, a ratio of 1.06. A formal check, Levene's test, asks whether the spreads differ; its p-value here is 0.402, so there's no evidence that they do.

If the SDs were very different, especially with unequal group sizes, you'd ask for Welch's ANOVA, which, like Welch's t-test, doesn't assume equal spreads.

10.4 Which Regions Differ? Tukey's HSD

ANOVA says the three means aren't all equal. It doesn't say which pairs differ. Maybe Metro is lower than the other two, which are the same. Maybe all three differ. To find out, you compare the pairs, but with a method built for multiple comparisons.

Tukey's HSD ("honestly significant difference") compares every pair of group means and widens each comparison's margin just enough that the chance of any false alarm across all the pairs stays at 5%. That's the "family-wise error rate" you'll see in some output.

Tukey's calculations use a distribution Excel doesn't have, so you'll get them from R. Here are the results for satisfaction by region:

Comparison	Difference in means	Adjusted p-value	95% interval
Mountain − Metro	1.35	< 0.001	1.06 to 1.64
Suburban − Metro	0.83	< 0.001	0.53 to 1.12
Suburban − Mountain	−0.52	< 0.001	−0.82 to −0.22

Read each row the same way. The difference is one region's mean minus the other's. The adjusted p-value already accounts for the three comparisons, so compare it with 0.05 directly. The interval gives a plausible range for the true difference; if it doesn't include 0, the pair differs.

All three intervals exclude zero. Every region differs from every other: Mountain customers are the most satisfied, Suburban customers are in the middle, and Metro customers are the least satisfied. The biggest gap, Mountain over Metro, is about 1.35 points on the 10-point scale.

10.5 Describe the Data to AI

Now run the analysis in R for Excel.

1. Open R for Excel. In 1. Data, click Add a table… and add Survey. Then add Stores the same way. The script needs both, because region is in Stores.
2. In 2. Ask your AI, type the data description below.
3. Click Copy prompt, paste it into your AI assistant, and send it.

Survey: each row is one Ridgeline customer's survey response (600 rows).
satisfaction is the customer's rating of the visit, from 1 (very dissatisfied)
to 10 (very satisfied); higher is better. store_id says which store the
customer visited.
Stores: each row is one of Ridgeline's 12 stores. region is Metro, Suburban
or Mountain. Survey has no region column, so region must be looked up from
Stores using store_id.

Question: Does average satisfaction differ among the three regions? If so,
which regions differ from which?

I'm in an intro business statistics course and have learned one-way ANOVA,
the F statistic, eta squared and Tukey's HSD.
Please run a one-way ANOVA of satisfaction by region and a Tukey HSD follow-up.
Also give the count, mean and SD of satisfaction for each region. Send the
results to Excel with excel_out(), and explain how to read them.

The most important sentence is the one about region. Without it, the AI sees two separate data frames and might not realize it has to join them, or might try to use store_id as the grouping variable, which would compare 12 stores instead of 3 regions.

10.6 Read the Script

A script for this usually looks like the one below.

# Look up each response's region from the Stores table
survey_region <- merge(Survey, Stores, by = "store_id")
survey_region$region <- factor(survey_region$region)

# Count, mean and SD of satisfaction in each region
region_summary <- data.frame(
  region = levels(survey_region$region),
  n      = as.vector(tapply(survey_region$satisfaction, survey_region$region, length)),
  mean   = as.vector(tapply(survey_region$satisfaction, survey_region$region, mean)),
  sd     = as.vector(tapply(survey_region$satisfaction, survey_region$region, sd)))
excel_out(region_summary, "Satisfaction by region")

# One-way ANOVA, then Tukey's HSD to see which regions differ
fit <- aov(satisfaction ~ region, data = survey_region)
excel_out(fit, "ANOVA: satisfaction by region")
excel_out(TukeyHSD(fit), "Which regions differ")

In plain language:

• merge joins the two data frames. For each survey response, it finds the row in Stores with the same store_id and adds that store's columns, including region. It's the R version of your XLOOKUP. The result still has 600 rows.
• factor tells R that region is a categorical variable whose values are group labels.
• The summary table uses tapply three times: split satisfaction by region, then count, average or take the SD of each piece. These should match your D1:G4 table exactly.
• aov fits the one-way ANOVA. The formula satisfaction ~ region reads "satisfaction by region": the outcome on the left of ~, the grouping variable on the right.
• TukeyHSD(fit) runs Tukey's comparisons on that ANOVA.

What to check: the outcome is satisfaction, the grouping variable is region (not store_id), and region comes from Stores. If the AI's script groups by store_id, it has answered a different question with 11 df between groups instead of 2.

10.7 Run It in R for Excel

Paste the AI's reply into 3. Run R code and click Run. Three output cards appear in Results. Click New sheet on each one, or Send everything to a new sheet.

If you get an error such as "object 'region' not found," the script tried to use region before merging, or merged the wrong tables. Click Copy a prompt to fix it and send that prompt back to the AI.

10.8 Interpret and Verify

The ANOVA card lands on the sheet as a small table with one row for region and one for Residuals ("residuals" is R's word for the within-group variation). Its columns are the degrees of freedom, sum of squares, mean square, F value and p-value. Match them to your RegionSat sheet:

R's row	df	Sum of squares	Mean square	F	p
region	2	189.26	94.63	60.46	< 0.001
Residuals	597	934.34	1.565

Every number should match your hand calculation. The Tukey card has one row per pair, with the difference (diff), the lower and upper ends of the interval (lwr, upr) and the adjusted p-value (p adj). R names each pair as "second level minus first level," for example Mountain-Metro, which is why the Mountain–Suburban comparison appears as Suburban-Mountain with a negative difference.

10.8.1 A second check with the ToolPak

Excel's Analysis ToolPak has a one-way ANOVA tool, Anova: Single Factor. It needs each group's values in a separate column, so first rearrange the data with FILTER.

1. On the RegionSat sheet, type Metro in N1, Mountain in O1 and Suburban in P1.
2. In N2 enter =FILTER($B$2#,$A$2#=N1) and copy it to O2 and P2. Each column spills one region's ratings: 217 for Metro, 190 for Mountain and 193 for Suburban. The columns have different lengths, and that's fine.
3. Choose Data > Data Analysis > Anova: Single Factor and click OK.
4. For Input Range, enter $N$1:$P$218 (the longest column, Metro, ends in row 218). Choose Grouped By: Columns, check Labels in first row, leave Alpha at 0.05, choose New Worksheet Ply and click OK.

The ToolPak's output has two parts. SUMMARY lists each group's count, sum, average and variance. ANOVA has rows for "Between Groups," "Within Groups" and "Total," and columns SS, df, MS, F, P-value and F crit. You should find SS 189.26 and 934.34, F 60.46, a P-value shown as about 1.2E-24, and F crit 3.011. Three tools, one answer. (The ToolPak isn't available in Excel for the web, but your formulas still work there.)

10.8.2 The conclusion, in business language

Customer satisfaction differs by region. In our survey of 600 customers, Mountain customers rated their visits 8.4 out of 10 on average, Suburban customers 7.8 and Metro customers 7.0. Each region differs from the other two by more than chance would explain. Region accounts for about 17% of the variation in ratings, so it matters, but individual customers within each region vary much more than the regions do. Metro is the region to focus on: its average is about 1.3 points below Mountain's.

That answers Marcus's question. It doesn't answer Rosa's real question, which is why. You'll come back to that at the end of the chapter.

10.9 When ANOVA Finds Nothing

Not every comparison turns up a difference, and a test that never says "no" would be useless. Try the same analysis on a different question: do customers spend different amounts depending on how they order? The survey's order_type column has three groups: Counter, Mobile and Drive-thru.

You don't need a lookup this time, since order_type is already in the Survey Table. Build the summary with structured references:

=AVERAGEIFS(Survey[spend],Survey[order_type],"Counter")

Order type	n	Mean spend	SD
Counter	345	$8.46	$2.64
Drive-thru	119	$8.44	$2.44
Mobile	136	$8.56	$2.58

The three means are within 12 cents of each other, while customers within each group vary by about $2.50. You can predict the result before running anything: the between-group variation is tiny compared with the within-group noise. The ANOVA confirms it: F = 0.09, p = 0.914. An F well below 1 means the group means are even closer together than random sampling usually puts them.

The conclusion is "no evidence of a difference," and it's useful. Ridgeline has wondered whether pushing customers to mobile ordering would cost it upselling at the counter. In this survey, mobile customers spend about the same as counter customers. Don't skip Tukey's HSD by accident here; skip it on purpose, because the ANOVA found nothing to follow up.

10.10 A Question ANOVA Can't Settle

Now look at wait times by order type. Same groups, different outcome:

Order type	Mean wait (minutes)	SD
Counter	5.10	2.33
Drive-thru	4.26	2.12
Mobile	2.97	1.83

This time the ANOVA is overwhelming: F = 46.6, p < 0.001. Tukey's HSD shows that all three order types differ. Mobile customers wait about 2.1 minutes less than counter customers, and drive-thru customers about 0.8 minutes less.

Waits differ by region too. In Chapter 8. Hypothesis Testing you saw that Metro customers wait 5.8 minutes on average, compared with 4.3 in Suburban stores and 3.1 in Mountain stores. Metro stores are the busiest, and Metro customers order at the counter more often.

That raises an awkward question about this chapter's main result. Are Metro customers less satisfied because of something about Metro, such as its customers or its stores? Or are they less satisfied because they wait longer, and long waits make anyone unhappy? The two explanations call for different actions. If it's the waits, Rosa should add staff at peak times or promote mobile ordering. If it's the region, those fixes might not help.

One-way ANOVA can't tell these explanations apart, because it looks at only one grouping variable at a time. Region and wait time are tangled together in the data, much as training and tenure were in Chapter 9. Comparing Two Groups. Untangling them takes a model with both in it at once. In Chapter 13. Multiple Regression you'll fit that model. As a preview: once wait time is in the model, Metro's satisfaction gap compared with Suburban shrinks from about 0.8 points to about 0.3, and Mountain's advantage over Suburban shrinks to about 0.1 and is no longer statistically significant. Most of what looked like a region effect is a waiting effect.

For now, the honest message to Rosa is: "Your customers really are less satisfied, by more than chance explains. Your customers also wait longer. Before we decide what to do, we'll check how much of the gap the waits explain."

10.11 Summary

When you compare three or more groups, running a t-test for every pair inflates the chance of a false alarm (with three tests at the 0.05 level, to about 14%). ANOVA asks one question instead: are all the group means equal? It compares between-group variation, how far the group means are from the grand mean, with within-group variation, the noise among individuals in the same group. The F statistic is the ratio of their mean squares. F near 1 is what equal means produce, and a large F, with a small p-value from F.DIST.RT, is evidence that at least one mean differs. You can build the whole calculation in Excel with DEVSQ, FILTER and a few arithmetic formulas, and check it with the ToolPak's Anova: Single Factor tool.

ANOVA's assumptions are independence, roughly normal group means (easy with large groups) and similar spreads in each group. Eta squared measures how much of the variation the groups explain. After a significant ANOVA, Tukey's HSD shows which pairs of groups differ while holding the overall false-alarm rate at 5%. At Ridgeline, satisfaction differs among all three regions (F = 60.5, η² = 0.17), spending doesn't differ by order type (p = 0.914), and the regional gap in satisfaction may be largely a gap in waiting times, a question that needs the multiple regression you'll learn later.