Chapter 11: Relationships in Categorical Data

Two years ago Ridgeline launched mobile ordering. Customers order and pay in the app, then pick up their drinks at a shelf by the door. Marcus Bell has a theory: "Mobile customers love it. I bet they're our biggest fans." Ridgeline's best free advertising is customers telling their friends, and the survey asked every customer whether they would recommend Ridgeline. So Marcus's question is: is the way a customer orders related to whether they would recommend us?

Both variables are categorical. order_type is Counter, Mobile or Drive-thru, and would_recommend is Yes or No. There are no means to compare, so the t-tests and ANOVA of the last two chapters don't apply. You need a test built for counts.

11.1 Prepare the Data in Excel

11.1.1 A contingency table with a PivotTable

The natural summary of two categorical variables is a table of counts, with one variable's categories down the side and the other's across the top.

Build one with a PivotTable:

1. Click any cell in the Survey Table and choose Insert > PivotTable. Check that the Table/Range box reads Survey, choose New Worksheet and click OK.
2. In the PivotTable Fields pane, drag order_type to Rows and would_recommend to Columns.
3. Drag response_id to Values. Because it's a text column, Excel counts it automatically, and the field shows as "Count of response_id."
4. Rename the sheet RecPivot.

You should see these counts:

Order type	No	Yes	Total
Counter	125	220	345
Drive-thru	35	84	119
Mobile	33	103	136
Total	193	407	600

The counts alone are hard to compare, because the groups are different sizes. 220 counter customers said Yes, more than twice as many as any other group, but there were also far more counter customers. To compare fairly, turn the counts into row percentages: within each order type, what percentage said Yes?

5. Drag response_id to Values a second time. Click the new "Count of response_id2" field in the Values box and choose Value Field Settings….
6. On the Show Values As tab, choose % of Row Total and click OK.

Order type	Recommend: No	Recommend: Yes
Counter	36.2%	63.8%
Drive-thru	29.4%	70.6%
Mobile	24.3%	75.7%
All customers	32.2%	67.8%

Now the pattern is visible. Three out of four mobile customers would recommend Ridgeline, compared with fewer than two out of three counter customers. Drive-thru customers are in between. Marcus's theory looks right so far. The question is whether the pattern is bigger than the luck of who answered the survey.

11.1.2 The same table with formulas

PivotTables are quick, but their layout can move when the data change, which makes them awkward to build formulas on. For the calculations in this chapter, rebuild the counts with COUNTIFS on a new sheet named ChiSq:

1. In B2 type No, in C2 Yes and in D2 Total. In A3:A5 type Counter, Drive-thru, Mobile, and in A6 type Total.

2. In B3 enter

   =COUNTIFS(Survey[order_type],$A3,Survey[would_recommend],B$2)

   and fill it across to C3 and down to row 5. The $ signs keep the formula looking at column A for the order type and at row 2 for the answer.

3. In D3 enter =SUM(B3:C3) and fill down to D5. In B6 enter =SUM(B3:B5) and fill across to D6.

The table should match the PivotTable exactly, with 600 in D6.

11.2 How the Chi-Square Test Works

11.2.1 What "no relationship" would look like

Start with the hypotheses.

• H₀: order type and recommending are independent. Knowing how a customer ordered tells you nothing about whether they'd recommend Ridgeline.
• Hₐ: they're related. The chance of recommending differs among order types.

If H₀ were true, what would the table look like? Overall, 407 of 600 customers, or 67.8%, would recommend Ridgeline. If order type didn't matter, each order type would have about that same rate. Of the 345 counter customers, you'd expect about 67.8% of 345, or 234.02, to say Yes. You'd expect the other 110.98 to say No.

Those are expected counts: what each cell would hold, on average, if the two variables were unrelated and the row and column totals stayed the same.

The formula is a shortcut for the reasoning above:

Expected count = (row total × column total) ÷ grand total

For counter customers who'd recommend: 345 × 407 ÷ 600 = 234.02. Build the whole expected table below the observed one on the ChiSq sheet:

1. Copy the headings: No and Yes in B9 and C9, the three order types in A10:A12.
2. In B10 enter =$D3*B$6/$D$6 and fill it across to C10 and down to row 12.

Order type	Expected No	Expected Yes
Counter	110.98	234.02
Drive-thru	38.28	80.72
Mobile	43.75	92.25

The $ signs matter. $D3 always takes the row total from column D, B$6 always takes the column total from row 6, and $D$6 is always the grand total. Check: the expected counts add up to the same row and column totals as the observed ones. Expected counts don't have to be whole numbers; they're averages.

Now compare observed with expected. Counter customers recommended less than independence predicts (220 Yes vs. 234.02 expected). Mobile customers recommended more (103 vs. 92.25). Drive-thru customers are close to expected (84 vs. 80.72).

11.2.2 Adding up the gaps

You need a single number that measures how far the whole observed table is from the expected table. For each cell, compute

(observed − expected)² ÷ expected

and add them up. That sum is the chi-square statistic, written χ² ("kai-square").

Each part of the formula has a reason.

• Subtract: the gap between what happened and what independence predicts.
• Square: so that gaps in both directions count as evidence, and don't cancel out. (In every row, the No and Yes gaps are equal and opposite, so unsquared they'd sum to zero.)
• Divide by expected: to put each gap in proportion. Being off by 10 matters a lot when you expected 40 and little when you expected 230.

Build it on the ChiSq sheet. Copy the headings again to B14:C14 and A15:A17, then:

3. In B15 enter =(B3-B10)^2/B10 and fill it across to C15 and down to row 17.
4. In A19 type chi-square, and in B19 enter =SUM(B15:C17).

Order type	No	Yes
Counter	1.772	0.841
Drive-thru	0.281	0.133
Mobile	2.640	1.252

The total is χ² = 6.919. The contributions show where the evidence comes from: mainly mobile customers who said No less often than expected (2.640) and counter customers who said No more often than expected (1.772).

11.2.3 Is 6.919 large?

Even if order type and recommending were completely unrelated, a sample of 600 would not match the expected counts exactly, so χ² would rarely be 0. How big does chance alone typically make it? That depends on how many cells are free to vary, measured by the degrees of freedom:

df = (number of rows − 1) × (number of columns − 1)

Here df = (3 − 1) × (2 − 1) = 2. Once you know the row and column totals, filling in just two cells determines the rest of the table.

If H₀ is true, χ² follows a chi-square distribution with that many degrees of freedom. It's a right-skewed distribution whose average equals its df. With 2 df, a χ² around 2 is typical under H₀, and a value above 5.99 happens only 5% of the time. Our 6.919 is in that top 5%.

Add the last steps on the ChiSq sheet:

Cell	Label (column A)	Formula (column B)	Result
B20	df	=(ROWS(B3:C5)-1)*(COLUMNS(B3:C5)-1)	2
B21	p-value	=CHISQ.DIST.RT(B19,B20)	0.031
B22	check with CHISQ.TEST	=CHISQ.TEST(B3:C5,B10:C12)	0.031
B23	Cramér's V	=SQRT(B19/(D6*(MIN(ROWS(B3:C5),COLUMNS(B3:C5))-1)))	0.107

CHISQ.DIST.RT gives the right-tail area beyond 6.919 in a chi-square distribution with 2 df. CHISQ.TEST does the entire test in one step: give it the observed range and the expected range, and it computes χ² and df itself and returns the p-value. Both give 0.031. (Notice that CHISQ.TEST needs the expected counts. It can't compute them for you, which is one more reason to build the expected table.)

With p = 0.031, below 0.05, you reject H₀. Order type and recommending are related. This is the chi-square test of independence.

11.2.4 The conditions

The chi-square test needs three things.

1. Counts of independent observations. Each customer appears in exactly one cell, and one customer's answer doesn't affect another's. The test must be run on counts, never on percentages or averages; a table of percentages gives a meaningless χ².
2. A reasonable sample. The test uses the chi-square distribution as an approximation, which works when the counts aren't too small. The usual rule: every expected count should be at least 5. Check the expected table, not the observed one. Here the smallest expected count is 38.28, so the condition is easily met.
3. Categories that don't overlap. Each customer is Counter, Mobile or Drive-thru, not two at once.

If some expected counts are below 5, combine sparse categories when that makes business sense (for example, "Mobile" and "Drive-thru" into "Not counter"), or ask your AI about an exact test.

11.2.5 How strong is the relationship?

A small p-value says the relationship is probably real. It doesn't say it's strong, and with large samples even weak relationships become significant. Cramér's V measures strength on a scale from 0 to 1.

Here V = √(6.919 ÷ (600 × 1)) = 0.107. A common guide for tables with two columns treats about 0.1 as weak, 0.3 as moderate and 0.5 as strong. So the relationship is real but weak. Most of what decides whether a customer would recommend Ridgeline has nothing to do with how they ordered. That fits the row percentages: 64% versus 76% is a noticeable gap, but most customers in every group would recommend.

11.3 Association Is Not Cause

Should Ridgeline push more customers to mobile ordering to create more fans? Not on this evidence alone. The chi-square test shows that order type and recommending go together. It doesn't show why.

Think about what's different about a mobile order. The customer skips the line. In the survey, mobile customers waited 2.97 minutes on average, compared with 5.10 minutes for counter customers and 4.26 for drive-thru. You saw in Chapter 10. Comparing Many Groups: ANOVA that those differences are far too large to be chance.

So here's a competing explanation: maybe customers who wait less are happier and more likely to recommend Ridgeline, however they ordered. Mobile customers just happen to wait less. You can check the first half of that with another chi-square test. Make a helper column that splits wait time into two groups. In a new sheet, or next to the Survey Table, count customers with

=COUNTIFS(Survey[wait_minutes],">5",Survey[would_recommend],"Yes")

and the matching formulas for "<=5" and for "No". The table:

Waited	Recommend: No	Recommend: Yes	% Yes
5 minutes or less	91	278	75.3%
More than 5 minutes	102	129	55.8%

This relationship is much stronger: χ² = 24.7, p < 0.001. Customers who waited more than five minutes were about 20 percentage points less likely to recommend Ridgeline. And mobile customers rarely wait that long: only 17 of 136 mobile customers waited more than five minutes, compared with 170 of 345 counter customers.

Wait time is a likely confounding variable, the idea you met in Chapter 9. Comparing Two Groups. It is related to the grouping (order type) and to the outcome (recommending), so it can create an association between them. If wait time is the real driver, then pushing customers to mobile helps only because it cuts their wait, and cutting counter waits directly would help too. The survey is an observational study, so no test on this table can settle the matter. In Chapter 12. Correlation and Simple Regression you'll measure how satisfaction falls with each extra minute of waiting, and in Chapter 13. Multiple Regression you'll see what happens to order type once wait time is taken into account.

11.4 Two More Tables

Practice reading two more contingency tables from the survey. Build each one with a PivotTable or COUNTIFS, exactly as before.

Loyalty membership and recommending. Ridgeline spends money on its loyalty program. Are members bigger fans?

Loyalty member	Recommend: No	Recommend: Yes	% Yes
No	115	238	67.4%
Yes	78	169	68.4%

The percentages are almost identical. χ² = 0.066 with 1 df, p = 0.797, and Cramér's V = 0.011. There's no evidence of any relationship. That's a useful finding, not a failed test. In Chapter 9. Comparing Two Groups you saw that members visit far more often than non-members. This table says they aren't more likely to recommend Ridgeline. The program buys frequency, not word of mouth.

Region and recommending. The survey doesn't include region, so look it up inside the count. The Yes count for Metro, for example, is

=SUM((XLOOKUP(Survey[store_id],Stores[store_id],Stores[region])="Metro")*(Survey[would_recommend]="Yes"))

XLOOKUP returns the region of all 600 responses, each comparison returns TRUE or FALSE for every row, multiplying them counts a row only when both are true, and SUM adds them up.

Region	Recommend: No	Recommend: Yes	% Yes
Metro	87	130	59.9%
Mountain	46	144	75.8%
Suburban	60	133	68.9%

χ² = 11.86 with 2 df, p = 0.003, and Cramér's V = 0.141. Recommending differs by region, in the same order as satisfaction did in the last chapter: Mountain highest, Metro lowest. The relationship is a bit stronger than the one with order type, but still weak. And since Metro customers wait longest, the same question about waiting applies.

11.5 Goodness of Fit: One Variable Against a Target

The chi-square idea works for one categorical variable too. Instead of asking whether two variables are related, you ask whether one variable's counts match a set of proportions you specify in advance. This is a goodness-of-fit test.

Example 1: Were the regions surveyed equally? Ridgeline's plan was to survey about the same number of customers in each region. The survey has 217 Metro, 190 Mountain and 193 Suburban responses. Is that consistent with an equal three-way split, or was one region over-sampled?

On a new sheet, put the regions in A2:A4, the observed counts in B2:B4 and the expected counts in C2:C4. With equal thirds, each expected count is 600 ÷ 3 = 200. You can type the observed counts or compute them with the XLOOKUP approach above, for example =SUM(--(XLOOKUP(Survey[store_id],Stores[store_id],Stores[region])=A2)). (The -- turns TRUE and FALSE into 1 and 0 so SUM can count them.)

Region	Observed	Expected	(O − E)² ÷ E
Metro	217	200	1.445
Mountain	190	200	0.500
Suburban	193	200	0.245
Total	600	600	χ² = 2.19

With 3 categories, df = 3 − 1 = 2. =CHISQ.TEST(B2:B4,C2:C4) returns the p-value directly: 0.335. (When the ranges are a single column, CHISQ.TEST uses the number of rows minus 1 as its df, which is right for goodness of fit.) The differences from 200 are well within what random variation produces. There's no evidence that any region was over- or under-sampled, which is reassuring for the regional comparisons in this chapter and the last.

Example 2: Were survey visits spread evenly over the week? Create the day of the week from visit_date with TEXT. The count for Monday is

=SUM(--(TEXT(Survey[visit_date],"ddd")="Mon"))

Day	Mon	Tue	Wed	Thu	Fri	Sat	Sun
Observed	85	79	90	82	91	88	85

Under "equally likely every day," each expected count is 600 ÷ 7 = 85.71. χ² = 1.30 with 6 df, and CHISQ.TEST gives p = 0.972. The counts are about as even as random sampling ever produces.

That matters for interpretation. Stores are much busier on weekends (in Chapter 4. Describing Data you saw about 408 transactions on an average Saturday, compared with 321 on a Monday). If surveys had been handed to customers at random, weekends would have more responses. Instead, the survey reached about the same number of customers every day, so weekend customers are slightly under-represented compared with their share of Ridgeline's business. A goodness-of-fit test can check whether a sample matches what you think it should, and the answer can raise questions about how the sample was collected.

11.6 Describe the Data to AI

Now run the order type analysis in R for Excel.

1. Open R for Excel. In 1. Data, click Add a table… and add Survey. Add Stores too, for the goodness-of-fit test by region.
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).
order_type is how the customer ordered: Counter, Mobile or Drive-thru.
would_recommend is Yes or No: would they recommend Ridgeline to a friend.
visit_date is the date of the visit. Region is not in Survey; it is in the
Stores table (one row per store), matched by store_id.

Questions:
1. Is order type related to whether a customer would recommend Ridgeline?
2. Goodness of fit: are survey responses split equally among the three
   regions, and equally among the seven days of the week?

I'm in an intro business statistics course and have learned contingency
tables, expected counts, the chi-square test of independence, Cramer's V and
the chi-square goodness-of-fit test.
Please make the table of counts, row percentages, the chi-square test, the
expected counts and Cramer's V for question 1, and the two goodness-of-fit
tests for question 2. Use chisq.test with correct = FALSE so the results match
Excel's CHISQ.TEST. Send each result to Excel with excel_out() and explain
how to read them.

11.7 Read the Script

A script for this usually looks like the one below.

# 1. Contingency table: order type by would recommend
rec_table <- table(Survey$order_type, Survey$would_recommend)
excel_out(rec_table, "Observed counts: order type by would recommend")
excel_out(round(100 * prop.table(rec_table, 1), 1), "Row percentages")

# Chi-square test of independence, and the counts expected if unrelated
rec_test <- chisq.test(rec_table, correct = FALSE)
excel_out(rec_test, "Chi-square test: order type and recommending")
excel_out(round(rec_test$expected, 2), "Expected counts")

# Cramer's V: strength of the relationship (0 = none, 1 = perfect)
cramers_v <- sqrt(rec_test$statistic / (sum(rec_table) * (min(dim(rec_table)) - 1)))
excel_out(round(unname(cramers_v), 3), "Cramer's V")

# 2. Goodness of fit: equal shares by region, and by day of the week
survey_region <- merge(Survey, Stores, by = "store_id")
excel_out(chisq.test(table(survey_region$region)), "Responses by region vs. equal thirds")
excel_out(chisq.test(table(weekdays(Survey$visit_date))), "Responses by day vs. equal sevenths")

In plain language:

• table counts the combinations of two columns: it's the R version of your PivotTable. prop.table(rec_table, 1) turns each row into proportions (the 1 means "by row"), and multiplying by 100 makes them percentages.
• chisq.test runs the test of independence on the table. Its result also stores the expected counts, which rec_test$expected pulls out so you can compare them with your ChiSq sheet.
• Cramér's V is computed from the test's χ² with the same formula you used in Excel.
• The goodness-of-fit tests give chisq.test a single count for each category. With only one set of counts and no proportions given, R tests "all categories equally likely." weekdays turns each visit date into a day name before counting.

What to check: the table crosses order_type with would_recommend (not, say, loyalty_member); the test is run on counts from table, not on the percentages; and region comes from merging with Stores.

11.8 Run It in R for Excel

Paste the AI's reply into 3. Run R code and click Run. Send each output card to a New sheet, or click Send everything to a new sheet. If R reports an error, use Copy a prompt to fix it and send it back to the AI.

11.9 Interpret and Verify

The chi-square result arrives as a two-column block headed "Pearson's Chi-squared test." Its key rows are X-squared (the χ² statistic), df and P-value. Check each against your ChiSq sheet:

On the R output sheet	Value	Excel check
X-squared	6.919	=SUM(B15:C17)
df	2	=(ROWS(B3:C5)-1)*(COLUMNS(B3:C5)-1)
P-value	0.031	=CHISQ.TEST(B3:C5,B10:C12)
Expected counts	110.98 … 92.25	your expected table, B10:C12
Cramér's V	0.107	your B23

The goodness-of-fit blocks are headed "Chi-squared test for given probabilities," with X-squared 2.19 (df 2, p = 0.335) for region and 1.30 (df 6, p = 0.972) for day of the week. The day-of-week table may list the days in alphabetical order (Friday, Monday, Saturday, …). That doesn't change the test, because every day has the same expected count.

11.9.1 The conclusion, in business language

Mobile customers are more likely to recommend Ridgeline: 76% would, compared with 71% of drive-thru customers and 64% of counter customers. The difference is larger than chance would explain (p = 0.031), but the relationship is weak. It is probably not about mobile ordering itself. Mobile customers wait about 3 minutes on average, compared with about 5 for counter customers, and customers who wait more than five minutes are much less likely to recommend us (56% vs. 75%). Shorter waits, whether through mobile ordering or faster counter service, look like the way to create more fans. Loyalty members are no more likely to recommend us than other customers.

11.10 Summary

When both variables are categorical, summarize them in a contingency table of counts and compare row percentages in the direction of the question. The chi-square test of independence asks whether the two variables are related. It compares each observed count with its expected count, the count you'd see on average if the variables were unrelated: row total × column total ÷ grand total. The chi-square statistic adds up (observed − expected)² ÷ expected over all cells, and its p-value comes from the chi-square distribution with (rows − 1) × (columns − 1) degrees of freedom. In Excel, CHISQ.DIST.RT gives the p-value from χ², and CHISQ.TEST gives it directly from the observed and expected ranges. The test needs counts of independent observations, and every expected count should be at least 5. Cramér's V measures the strength of the relationship. A goodness-of-fit test applies the same idea to one variable, checking its counts against proportions you specify.

At Ridgeline, order type is related to recommending (χ² = 6.92, p = 0.031, V = 0.107), but weakly, and wait time is a likely reason: mobile customers wait less, and customers who wait less recommend more. A significant association doesn't show cause. When you run these tests in R for Excel, make sure the test is run on counts, and remember that R's chisq.test corrects 2×2 tables by default, so ask for correct = FALSE when you want it to match Excel.