Which cell answers which question

The table that previews the pivot

The most common summary on any quiz is a small summary-by-group table: one row per category, with the count, the total and the mean of a numeric variable. Reading it correctly means matching the question to the cell — a skill Module 7 industrialises with pivots, but which starts with three functions.

Worked example — amount by province

prov   n    sum     mean
AB     2   2300  1150.00
BC     3   1360   453.33
ON     4   1995   498.75
QC     3    485   161.67

The n column is Lesson 4.1's COUNTIF. The sum is =SUMIF($B$2:$B$13, H2, $D$2:$D$13) — range, criterion, then the range to add. The mean is

=AVERAGEIF($B$2:$B$13, "BC", $D$2:$D$13)

— the same three arguments in the same order: the range to test, the criterion, the range to average. Filled down with H2 in place of "BC" it produces the whole column. In R, one call does the mean for every group at once:

> tapply(orders$amount, orders$prov, mean)
       AB        BC        ON        QC 
1150.0000  453.3333  498.7500  161.6667 

tapply(values, groups, function) — the values, the grouping variable, the function to apply. Swap mean for sum or length and you have the other two columns. The output is a named vector, groups in alphabetical order, and round(…, 2) trims it to 1150.00 · 453.33 · 498.75 · 161.67.

Which cell answers which question

How much did Ontario sell? — the ON sum, 1995. What is a typical Quebec order? — the QC mean, 161.67. How many BC orders were there? — the BC n, 3. What is a typical order overall? — none of these; the grand mean 511.67 is not the average of the four province means (it would be 565.94, which weights two Alberta orders as heavily as four Ontario ones). Each question names one cell, and the rounding rule — "to 2 decimal places" — belongs in the question, because 453.33 and 453.3 are both the BC mean.

The first unfair comparison

"Which province sells the most?" Read the sum column and the answer is Alberta, 2300 — more than Ontario's 1995. But the n column says Alberta did that on two orders and Ontario on four; one of Alberta's two is the $1,320 order flagged in Lesson 4.4. Comparing raw totals across groups of different size is the first unfair comparison the course names, and the mean column does not rescue it: an average of two values is not evidence of anything about Alberta. The honest reading is "AB leads on total but on a sample too small to support a decision" — which is precisely the evaluation step of the Alberta question from Module 1.

Type both calls, then read two cells

Type the AVERAGEIF and the tapply. The two numeric items ask you to read the BC mean and the ON total off the table; the code block rebuilds the mean column in plain Python, grouping the amounts by province in a dictionary of lists.

CRISP-DM: the summary-by-group table is data understanding → explore data; noticing that Alberta's lead rests on two orders is evaluation → evaluate results.