Module 1 — Full Test Bank

NoteHow this test bank works

This bank holds 120 questions: 10 per section, for each of 4 sections, across 3 datasets. On the real test you receive four questions — one from each section — all drawn from the same dataset, and you submit an .xlsx workbook with a separate sheet for each section’s answers.

Expand each Answer block to check your work. Every answer value is verified against the real data (as of the current snapshot); when the source data is refreshed the exact numbers may shift slightly, but the method stays the same.

The three datasets.

• Saskatchewan RM Crop Yields (1990–2025). Average yield (bu/ac) for eight crops in every Rural Municipality. Wide file rm_yields_1990plus.csv for Sections 1–3; long file rm_yields_1990plus_long.csv for Section 4. Blank = crop not grown/reported that RM-year.
• Manitoba Wheat Variety (2020–2025). Red spring wheat yield by variety and municipality. Reported = FALSE marks a yield suppressed by MASC because too few farms grew that variety there.
• Canada Field Crops (StatsCan, 2015–2025). Seeded acres and average yield (bu/ac) for major field crops, by province.

Grading model. Visual appearance 20%, plus 20% for each of the four sections (Descriptive, Conditional, Charts, PivotTables) = 100%. Charts and PivotTables are marked partly on how clearly they are presented — titles, axis labels, formatting.

Vocabulary note. Everything here uses only Module 1 tools: mean/median/mode, range, IQR, variance, standard deviation, coefficient of variation, percentiles/quartiles, symmetric vs skewed shape, outliers (the 1.5×IQR rule), histograms, box plots, chart choice, and PivotTables.

---

Section 1 — Descriptive Statistics

Questions 1–10 · Saskatchewan RM Crop Yields

Question 1

Using the Saskatchewan RM Crop Yields dataset.

Filter to Year = 2023 and work with the Canola column.

(a) Compute the mean and the median canola yield.

(b) Compare the two values. What does the relationship between the mean and the median tell you about how yields were distributed across RMs that year, and about the low-yielding RMs in particular?

(c) Compute the standard deviation and the interquartile range (IQR) of the same column.

(d) The IQR ignores the most extreme RMs while the standard deviation does not. Given what you found in (b), explain which of these two spread measures you would trust more to describe a “typical” amount of variation across RMs, and why.

(e) A classmate uses the mean to describe “a typical RM.” Do you think the mean or the median better represents a typical RM here? Justify your choice using your answers to (a)–(d).

Answer

• 1. mean ≈ 33.88, median ≈ 35.50 bu/ac.
• 2. The mean falls below the median → a left/low tail: a minority of RMs with unusually low yields (down to ~3 bu/ac) pull the mean beneath the median. Most RMs cluster higher.
• 3. SD ≈ 13.33 bu/ac; IQR ≈ 22.30 bu/ac (Q1 ≈ 22.60, Q3 ≈ 44.90).
• 4. The IQR is more robust to the low-yield tail; the SD is inflated by the extreme low RMs. Either defensible if tied to the skew.
• 5. The median — the mean is dragged down by the low tail. Credit for naming the median and connecting it to the skew.

Question 2

Using the Saskatchewan RM Crop Yields dataset.

Still on Year = 2023, compare the Canola column and the Barley column.

(a) Compute the mean and standard deviation of each crop’s yield.

(b) Compare the two standard deviations. If one crop’s is larger, does that by itself prove its yields were “more variable” in a meaningful sense? Explain the trap in comparing raw standard deviations across two crops with different averages.

(c) Compute the coefficient of variation (CV) for each crop.

(d) Using the CVs, state which crop’s yields were more variable relative to their average, and by how much in rough terms.

(e) Write one sentence, suitable for a farm newspaper, that correctly compares the variability of the two crops without misleading the reader.

Answer

• 1. Canola: mean ≈ 33.88, SD ≈ 13.33. Barley: mean ≈ 54.56, SD ≈ 24.60.
• 2. No — barley also has a much higher mean, so a bigger SD is partly just scale. Comparing raw SDs across crops with different means is the trap.
• 3. CV = SD/mean. Canola ≈ 0.39; Barley ≈ 0.45.
• 4. Barley is slightly more variable relative to its mean (0.45 vs 0.39) — a modest difference.
• 5. e.g. “Relative to their average yields, Saskatchewan barley yields in 2023 were a little more variable than canola (CV 0.45 vs 0.39).”

Question 3

Using the Saskatchewan RM Crop Yields dataset.

Compare Canola yields in a normal year and a drought year: filter first to Year = 2019, then to Year = 2021.

(a) Compute the mean and median canola yield for each of the two years.

(b) Compute the standard deviation and CV for each year.

(c) The CV is much higher in one of these years. Identify which, and explain in plain language what a higher CV means about how RMs fared relative to each other that year.

(d) Explain why a drought might make yields not just lower on average but also more variable across RMs (think about which RMs get rain and which don’t).

(e) Which single year would you call “more predictable” for a canola grower, and which statistic supports that?

Answer

• 1. 2019: mean ≈ 40.78, median ≈ 42.10. 2021: mean ≈ 21.86, median ≈ 22.80 bu/ac.
• 2. 2019: SD ≈ 8.17, CV ≈ 0.20. 2021: SD ≈ 8.14, CV ≈ 0.37.
• 3. 2021 has the far higher CV — relative to a low average, RMs differed a lot; some did much better or worse than others.
• 4. In drought, localized rain matters more — RMs that caught a storm did okay, those that missed it failed, widening the spread relative to the (low) mean.
• 5. 2019 is more predictable — its much lower CV (0.20 vs 0.37) shows tighter relative variation.

Question 4

Using the Saskatchewan RM Crop Yields dataset.

Filter to Year = 2023 and work with the Oats column.

(a) Compute the mean, median, and the 90th percentile of oat yield.

(b) State in plain language what the 90th percentile value tells you about RMs that year.

(c) Compute Q1, Q3, and the IQR.

(d) Compute the mean and median. Compare them and describe the shape of the distribution in Module-1 terms.

(e) Compare the IQR you found in (c) to the mean from (a) by computing the ratio IQR ÷ mean. Explain what a large value of this ratio tells you about how spread out oat yields were relative to their typical level.

Answer

• 1. mean ≈ 71.14, median ≈ 75.90, P90 ≈ 116.72 bu/ac.
• 2. 90% of reporting RMs yielded ~117 bu/ac or less; only the top 10% exceeded it.
• 3. Q1 ≈ 44.98, Q3 ≈ 96.50, IQR ≈ 51.52 bu/ac.
• 4. Mean (71.1) below median (75.9) → slight left/low skew.
• 5. IQR ÷ mean ≈ 51.52 / 71.14 ≈ 0.72 — the middle-50% spread is nearly three-quarters of the typical yield, a large relative spread: RMs differed widely compared with the average level.

Question 5

Using the Saskatchewan RM Crop Yields dataset.

Filter to Year = 2015 and work with the Flax column.

(a) Compute the mean, median, standard deviation, and CV.

(b) Flax has a lower average yield than most other crops. Explain why comparing flax’s standard deviation directly against, say, barley’s would be misleading, and what statistic fixes that.

(c) Compute the range (max − min).

(d) Explain why the range is a fragile measure of spread compared with the IQR, using this column as an example.

(e) If you had to report a single “typical flax yield” and a single “typical spread” for 2015, which two statistics would you choose, and why?

Answer

• 1. mean ≈ 23.62, median ≈ 23.64, SD ≈ 5.62, CV ≈ 0.238.
• 2. Flax’s mean is low, so a small SD is expected on scale alone; the CV (SD/mean) compares relative variability fairly across crops.
• 3. range ≈ 40.15 − 4.25 ≈ 35.9 bu/ac.
• 4. The range depends on only the two most extreme RMs, so one unusual value swings it; the IQR uses the middle 50% and is stable.
• 5. Median (≈23.6) for centre and IQR (≈7.0) for spread — both resist extreme values. (Mean/SD acceptable given the near-symmetry, if justified.)

Question 6

Using the Saskatchewan RM Crop Yields dataset.

Filter to Year = 2021 (a drought year) and work with the Durum column.

(a) Compute the mean and median durum yield.

(b) The mean sits above the median this time. Describe the shape this implies, and contrast it with the left-skew seen in the 2023 canola data.

(c) Compute the CV.

(d) This is one of the highest CVs of any crop-year in the dataset. Explain, in terms a grower would understand, what such a high CV says about the 2021 durum crop across RMs.

(e) Explain why, in a year like this, quoting only the mean durum yield to describe the province would hide something important — and what you would add to tell the fuller story.

Answer

• 1. mean ≈ 21.69, median ≈ 17.20 bu/ac.
• 2. Mean above median → a right/high tail (a minority of RMs did much better than the struggling majority) — the mirror image of the 2023 canola left-skew.
• 3. CV ≈ 0.57.
• 4. Enormous relative spread — RMs’ fortunes diverged sharply; some near-failed while others were only moderately hit.
• 5. The mean alone hides that most RMs were below it (right-skew) and that spread was huge — add the median and the CV/IQR to show the typical RM did worse than the average suggests.

Question 7

Using the Saskatchewan RM Crop Yields dataset.

Filter to Year = 2023 and compare Spring Wheat and Peas.

(a) Compute the mean and standard deviation of each.

(b) Compute the CV of each.

(c) The two CVs are similar. Explain what it means for two crops with very different average yields to have similar CVs.

(d) Compute the median of each and compare it to the mean. Do both crops show the same direction of skew?

(e) A student concludes “wheat and peas are equally risky to grow.” Explain one thing this CV comparison does, and one thing it does not, support about that claim.

Answer

• 1. Spring Wheat: mean ≈ 42.87, SD ≈ 17.22. Peas: mean ≈ 33.96, SD ≈ 13.19.
• 2. Spring Wheat CV ≈ 0.40; Peas CV ≈ 0.39.
• 3. Their yields vary by a similar proportion of their own averages, even though wheat yields more in absolute bu/ac.
• 4. Wheat: mean < median (left skew). Peas: mean ≈ median (roughly symmetric). Not the same.
• 5. Supports: similar relative yield variability. Does not support: “risk” also depends on price, cost, and market — not shown here.

Question 8

Using the Saskatchewan RM Crop Yields dataset.

Work with the Canola column across all years (do not filter by year).

(a) Compute the overall mean and median canola yield for 1990–2025.

(b) Compute the overall (all-years pooled) standard deviation.

(c) Now compute the standard deviation within just 1995 and within just 2023 (filter to each year). Compare these two within-year SDs to the pooled SD from (b), and note whether the pooled value falls above, below, or between them.

(d) Compute the average canola yield for Year = 1995 and for Year = 2020 separately. Using just these two numbers, describe how much average canola yield changed between an early and a recent year.

(e) Based on what you found in (d), explain why an overall 1990–2025 mean is a poor description of what a grower should expect in a recent year.

Answer

• 1. mean ≈ 28.29, median ≈ 26.90 bu/ac (as of snapshot).
• 2. pooled SD ≈ 10.06 bu/ac.
• 3. Within-year SDs: 1995 ≈ 4.48 (tight), 2023 ≈ 13.33 (wide). The pooled SD (10.06) falls between them — early years were tightly clustered, recent drought-affected years spread out more, and pooling all years lands in the middle. (So a pooled SD is not automatically larger than every within-year SD.)
• 4. 1995 canola averages ≈ 19.2 vs 2020 ≈ 38.2 bu/ac — roughly double; average yields rose markedly from the mid-1990s to recent years.
• 5. Because recent years read much higher than early ones, a 36-year average (~28) sits well below recent levels and understates a recent-year expectation.

Question 9

Using the Saskatchewan RM Crop Yields dataset.

Filter to Year = 2023 and work with the Barley column.

(a) Compute Q1, Q3, and the IQR.

(b) Using the 1.5×IQR rule, compute the lower and upper “fences” (Q1 − 1.5·IQR and Q3 + 1.5·IQR).

(c) Count how many RMs (if any) fall outside those fences — i.e. would be flagged as outliers on a box plot.

(d) The minimum barley yield this year is about 2.9 bu/ac. Explain whether the 1.5×IQR rule flags it as an outlier, and why a genuinely low value might not be flagged when the spread is already very wide.

(e) Explain in one sentence why a wide IQR makes the outlier fences more forgiving.

Answer

• 1. Q1 ≈ 32.60, Q3 ≈ 74.00, IQR ≈ 41.40 bu/ac.
• 2. Lower fence ≈ 32.60 − 62.10 ≈ −29.5; upper ≈ 74.00 + 62.10 ≈ 136.1.
• 3. Zero RMs fall outside — even the ~2.9 min is above −29.5.
• 4. The min (2.9) is above the lower fence, so it is not flagged; the very wide IQR (41.4) pushes the fence far below any real value.
• 5. A wider IQR makes 1.5×IQR larger, so the fences sit farther from the quartiles and catch fewer points.

Question 10

Using the Saskatchewan RM Crop Yields dataset.

Work with the Spring Wheat column and compare Year = 2015 with Year = 2019 (two non-drought years).

(a) Compute the mean and median spring wheat yield for each year.

(b) Compute the standard deviation and CV for each year.

(c) State which year had the higher average, and whether the higher-average year was also more or less variable in relative terms.

(d) Explain why two “good” years can still differ noticeably in average yield (name one factor).

(e) Describe, in one sentence, how you would summarise the difference between these two years to a non-technical reader using the statistics you computed.

Answer

• 1. 2015: mean ≈ 37.02, median ≈ 37.49. 2019: mean ≈ 49.23, median ≈ 50.05 bu/ac.
• 2. 2015: SD ≈ 8.67, CV ≈ 0.234. 2019: SD ≈ 11.11, CV ≈ 0.226.
• 3. 2019 is higher on average (~12 bu/ac more); its CV (0.226) is about equal to 2015’s (0.234), so it was not more variable in relative terms.
• 4. Growing-season weather differs year to year even between two good years (rainfall timing, heat, frost). Any sensible factor credited.
• 5. e.g. “2019 spring wheat yielded about 12 bu/ac more than 2015 on average, with similar relative variability across RMs.”

Questions 11–20 · Manitoba Wheat Variety

Question 11

Using the Manitoba Wheat Variety dataset.

Work with the reported yields only (Reported = TRUE).

(a) Compute the mean and the median of the reported yields.

(b) Compare the two values, and describe in Module-1 terms what their relationship implies about the shape of the yield distribution.

(c) Compute the standard deviation, the IQR, and the coefficient of variation.

(d) The CV for Saskatchewan 2023 canola was about 0.39. Compare it to the CV you found here and interpret the contrast in one sentence a grower would understand.

(e) Based only on the shape and the CV you computed, would you describe this dataset as more or less consistent than the Saskatchewan 2023 canola data? Point to the specific numbers that justify your answer.

Answer

• 1. mean ≈ 61.16, median ≈ 62.20 bu/ac.
• 2. Mean ≈ median → roughly symmetric, unlike the left-skewed SK canola.
• 3. SD ≈ 12.38, IQR ≈ 15.70, CV ≈ 0.20.
• 4. CV ≈ 0.20 vs ≈ 0.39 — e.g. “these wheat yields vary about half as much, relative to their average, as SK canola did in 2023.”
• 5. More consistent — justified by the lower CV (0.20 vs 0.39) and the roughly symmetric shape (mean ≈ median) versus the SK left skew. Answer must cite the numbers, not outside reasoning.

Question 12

Using the Manitoba Wheat Variety dataset.

Continue with the reported yields.

(a) Compute the 90th percentile of the reported yields and state, in plain language, what it means.

(b) Compute the minimum reported yield.

(c) Compute how many standard deviations the minimum sits below the mean (that is, (mean − minimum) ÷ SD). Explain what this distance tells you about how extreme the lowest value is.

(d) Explain what would happen to the mean, versus the median, if that single lowest value were removed — and why the two measures respond differently to one extreme value.

(e) Which single number — mean or median — would you headline in a report titled “typical Manitoba wheat yield,” and why?

Answer

• 1. P90 ≈ 75.8 bu/ac — 90% of reported results were at or below this.
• 2. minimum ≈ 4.5 bu/ac.
• 3. (61.16 − 4.5) / 12.38 ≈ 4.6 standard deviations below the mean — a very extreme low value, far outside the bulk of the data.
• 4. Removing it would raise the mean noticeably (mean sums every value) but barely move the median (a position).
• 5. The median — not distorted by the low extreme, so it better represents “typical.”

Question 13

Using the Manitoba Wheat Variety dataset.

Filter the reported yields to a single year, Year = 2021, and then to Year = 2023.

(a) Compute the mean and median reported yield for each of the two years.

(b) Compute the standard deviation and CV for each year.

(c) 2021 was a drought year on the Prairies. State whether the Manitoba variety data shows a lower average that year, and by roughly how much.

(d) Compare the CVs of the two years and say whether the drought year was also more variable in relative terms.

(e) The Saskatchewan RM canola CV was about 0.37 in the 2021 drought, while this Manitoba wheat CV stayed near 0.25. Using only these numbers, state which dataset’s yields were disturbed more by the drought in relative terms.

Answer

• 1. 2021: mean ≈ 49.64, median ≈ 50.55. 2023: mean ≈ 61.42, median ≈ 61.90 bu/ac. (2021 lower.)
• 2. 2021: SD ≈ 12.20, CV ≈ 0.246. 2023: SD ≈ 11.27, CV ≈ 0.184.
• 3. Yes — 2021 averages ~12 bu/ac below 2023, a clear drought drop.
• 4. 2021’s CV (0.246) is noticeably higher than 2023’s (0.184) — the drought year was more variable in relative terms, though both stay well below SK field CVs.
• 5. The Saskatchewan data was disturbed more in relative terms — its drought-year CV (~0.37) is much higher than the Manitoba figure (~0.25), so its yields spread out more relative to their average.

Question 14

Using the Manitoba Wheat Variety dataset.

Filter the reported yields to the single variety AAC BRANDON (BW 932) (the most widely grown).

(a) Compute the mean, median, and standard deviation of this variety’s reported yields.

(b) Compute its CV.

(c) The CV of all reported wheat yields pooled together (every variety) is about 0.20. Compare this single variety’s CV to that pooled figure. Is a single variety more or less variable than the whole mix of varieties, and does that make sense?

(d) Explain why pooling many different varieties together could make the overall spread larger than any single variety’s spread.

(e) State, in one sentence, why a grower comparing varieties should look at each variety’s own spread, not just the overall average across all varieties.

Answer

• 1. mean ≈ 59.6, median ≈ 60.x, SD ≈ 11.1 bu/ac.
• 2. CV ≈ 0.19.
• 3. Similar to or slightly below the pooled CV (~0.20) — a single variety is about as variable, since most spread is site/year, not variety.
• 4. Different varieties have different average yields, so mixing them adds between-variety differences to the spread.
• 5. Overall spread blends variety differences with site/weather; to compare varieties fairly you need each variety’s own centre and spread.

Question 15

Using the Manitoba Wheat Variety dataset.

Compare two specific varieties across their reported yields: SY MANNESS and AAC BRANDON (BW 932).

(a) Compute the mean yield of each variety.

(b) Compute the standard deviation of each.

(c) One variety has a higher average. State which, and by roughly how much.

(d) Compute how many reported results back each average (a simple count). Explain why the variety with the higher average might still be the riskier choice if it has far fewer reports.

(e) Explain, in one sentence, why “highest average yield” alone is not enough to recommend a variety.

Answer

• 1. SY MANNESS mean ≈ 72.1; AAC Brandon mean ≈ 59.6 bu/ac.
• 2. SY MANNESS SD ≈ 11.5; AAC Brandon SD ≈ 11.1.
• 3. SY MANNESS is higher by ~12 bu/ac.
• 4. SY MANNESS n ≈ 108 vs AAC Brandon n ≈ 519 — a much thinner track record, so its average is less proven and could reflect favourable sites.
• 5. A high average on few reports may not generalize; consistency and breadth of testing matter too.

Question 16

Using the Manitoba Wheat Variety dataset.

Work with the reported yields for Year = 2023.

(a) Compute the mean, median, and CV.

(b) Compute Q1, Q3, and the IQR.

(c) Using the 1.5×IQR rule, compute the lower fence and state whether any reported yields fall below it.

(d) Explain what it means that this dataset does produce low-end outliers by the 1.5×IQR rule, whereas very wide-spread data (like Saskatchewan barley yields, which have an IQR of about 41 bu/ac) can have no flagged outliers even with some very low values.

(e) State, in one sentence, why “has outliers” depends on how tight the bulk of the data is, not just on how low the lowest value is.

Answer

• 1. mean ≈ 61.4, median ≈ 61.9, CV ≈ 0.18.
• 2. Q1 ≈ 55.1, Q3 ≈ 69.25, IQR ≈ 14.15.
• 3. Lower fence ≈ 55.1 − 21.2 ≈ 33.9; several low values (e.g. ~4.5, ~18–33) fall below → flagged as outliers.
• 4. The MB bulk is tight (small IQR), so the fences sit close and low values get flagged; SK barley’s huge IQR pushed its fences far out, flagging none.
• 5. Outlier status is relative to the spread of the middle 50%; a tight bulk makes moderate departures stand out.

Question 17

Using the Manitoba Wheat Variety dataset.

Consider the whole file, including suppressed rows.

(a) Using the Reported column, count how many rows are reported (TRUE) and how many are suppressed (FALSE).

(b) If you computed the mean of Yield_bu_ac over the whole file (including suppressed rows), explain what the blank suppressed cells would do to that mean — and why it still returns a sensible number.

(c) Explain why the count of reported vs suppressed rows is itself informative, even though the suppressed rows carry no yield number.

(d) A student says “most varieties in most places have no data, so this dataset is useless.” Give a more accurate interpretation of all those suppressed rows.

(e) State the one-sentence rule this illustrates about distinguishing “a small measured value” from “a value withheld.”

Answer

• 1. reported ≈ 2398 TRUE, suppressed ≈ 2960 FALSE.
• 2. Excel’s AVERAGE skips blank cells, so it averages only reported yields — the suppressed blanks are ignored, not treated as 0.
• 3. It shows how widely each variety was grown/reported — a coverage signal, useful on its own.
• 4. The variety was grown there; the number was just withheld for privacy (too few farms). Absence of a number ≠ absence of the crop.
• 5. A blank means “withheld / no data”; never read it as a measured low value or as zero.

Question 18

Using the Manitoba Wheat Variety dataset.

Filter the reported yields to Year = 2025.

(a) Compute the mean and median.

(b) Compute the standard deviation and CV.

(c) Compare the 2025 average to the 2021 average (drought year) you would find by refiltering. State roughly how much higher 2025 is.

(d) Explain why comparing a variety program’s results across years requires care when the set of varieties tested changes from year to year.

(e) Suggest one way, using Module-1 tools, to make a fairer year-to-year comparison than just comparing overall means (hint: hold something constant).

Answer

• 1. 2025: mean ≈ 68.03, median ≈ 69.00 bu/ac.
• 2. SD ≈ 11.84, CV ≈ 0.174.
• 3. 2025 (≈68.0) averages well above 2021 (≈49.6) — about 18 bu/ac higher, reflecting a non-drought year.
• 4. If different varieties are tested each year, an average shift could reflect the mix of varieties, not the growing conditions.
• 5. Compare a single common variety (e.g. AAC Brandon) across years, holding genetics constant so the year effect is isolated.

Question 19

Using the Manitoba Wheat Variety dataset.

Filter the reported yields to the municipality RIDING MOUNTAIN WEST in Year = 2025 (eleven varieties were reported there that year).

(a) Compute the mean and range of the reported yields within that municipality-year.

(b) Explain what the range here represents (it is a spread across varieties in one place and year, not across places).

(c) Explain why this within-municipality spread isolates variety differences better than the province-wide spread does.

(d) State one limitation of drawing variety conclusions from a single municipality-year.

(e) Describe, in one sentence, how you would combine several municipalities to make a variety comparison more trustworthy.

Answer

• 1. mean ≈ 71.7 bu/ac; range ≈ 87.7 − 43.4 ≈ 44.3 bu/ac (11 varieties reported).
• 2. The range is the gap between the best- and worst-yielding varieties in that one place and year.
• 3. Same place, same weather, same soil → differences are mostly genetics (variety), not environment.
• 4. One site-year is a single sample; weather/soil there may not represent the province, and chance plays a role.
• 5. Average each variety across many municipalities (e.g. a PivotTable by variety) so site quirks average out.

Question 20

Using the Manitoba Wheat Variety dataset.

Consider the reported yields for the two most widely reported varieties (by count).

(a) Identify the two varieties with the most reported rows, and compute the mean of each.

(b) Compute the CV of each.

(c) State which of the two is more consistent (lower CV), and what “more consistent” means for a grower.

(d) For each of the two varieties, the CV and the report count are both available to you. Explain what each of those two numbers separately tells you about how much to trust the variety’s average.

(e) Summarise, in one sentence, how a lower CV and a higher report count each make a variety’s average yield more dependable as a summary.

Answer

• 1. Most-reported: AAC Brandon (n≈519, mean≈59.6) and AAC Starbuck (n≈374, mean≈62.7).
• 2. AAC Brandon CV ≈ 0.19; AAC Starbuck CV ≈ 0.18 — very close.
• 3. The lower-CV variety is more consistent — its values sit closer to the mean, so the average describes it well.
• 4. The CV tells you how tightly the yields cluster around the mean (lower = the mean is more representative); the count tells you how many results back the mean (more = the average is less likely to be a fluke).
• 5. e.g. “A lower CV means the average sits among tightly clustered values, and a higher count means it rests on more evidence — both make the average more dependable.”

Questions 21–30 · Canada Field Crops

Question 21

Using the Canada Field Crops dataset.

Filter to Spring wheat (all provinces, all years).

(a) Compute the mean, median, and range of spring wheat yield.

(b) The range is large. Explain what pooling many provinces (a maritime province together with the Prairies) does to a single national mean, and why that mean might describe no actual province well.

(c) Compute the standard deviation and the coefficient of variation of the pooled spring wheat yields.

(d) Suggest a better way, using only Module-1 tools, to summarise “spring wheat yield in Canada” than one overall mean and standard deviation.

(e) Explain, in one sentence, why “the average Canadian spring wheat yield” can be technically correct but practically misleading.

Answer

• 1. mean ≈ 51.3, median ≈ 50.8, range ≈ 33.7 to 73.9 bu/ac.
• 2. Pooling very different provinces produces a mean that sits between groups and matches none of them.
• 3. SD ≈ 7.5, CV ≈ 0.15.
• 4. Break out by province (AVERAGEIF/PivotTable), and/or a box plot per province — summarise the groups.
• 5. A national average blends different growing regions, so it can be arithmetically right yet represent no real farm.

Question 22

Using the Canada Field Crops dataset.

Filter to Canola (all provinces, all years).

(a) Compute the mean, standard deviation, and coefficient of variation of canola yield.

(b) The CV of provincial spring wheat yields (all provinces, all years) is about 0.15. Compare this canola CV to that, and state which crop’s provincial yields are more consistent across the country.

(c) In the raw StatsCan table (before cleaning), one province-year listed a canola yield of “0 bu/ac.” Explain why treating that 0 as a real yield would distort the mean and the standard deviation.

(d) Explain what that 0 most likely represented, and why the cleaned file treats it as missing rather than as the number zero.

(e) State the general rule this illustrates about the difference between “a measured value of zero” and “no data.”

Answer

• 1. mean ≈ 41.0, SD ≈ 5.35, CV ≈ 0.13.
• 2. Canola CV (≈0.13) below spring wheat’s (≈0.15) → canola’s provincial yields are slightly more consistent.
• 3. A false 0 pulls the mean down and inflates the SD (it looks like a huge deviation), corrupting both.
• 4. It was a placeholder for negligible/near-zero acreage (e.g. Newfoundland), not a real yield — so treated as missing.
• 5. A blank means “no data”; 0 means “a real measurement of zero.” Never substitute one for the other.

Question 23

Using the Canada Field Crops dataset.

Filter to Saskatchewan (all crops, all years), then look at the Yield_bu_ac column.

(a) Compute the mean and median yield across all Saskatchewan crop-year rows.

(b) Explain why mixing different crops (canola, wheat, oats, etc.) into one mean makes that mean hard to interpret.

(c) Explain the difference between this problem and the problem of pooling many provinces into one national average — i.e. pooling crops vs pooling places. Both blend unlike things into one number; describe what each one hides.

(d) Suggest the right Module-1 tool to separate the crops before summarising.

(e) State the one-sentence lesson about what you must hold constant before an average is meaningful.

Answer

• 1. mean ≈ 45.1, median ≈ 40.4 bu/ac across all SK crop-year rows (a mix of crops — deliberately hard to interpret).
• 2. Different crops have very different typical yields, so their combined mean is a meaningless blend (oats ~70 with flax ~20, etc.).
• 3. Same underlying issue — pooling unlike things. Here the unlike dimension is crop; in Q21 it was province. Both hide structure.
• 4. A PivotTable (Crop on Rows, Yield as Average) — or AVERAGEIF per crop — to summarise each crop separately.
• 5. An average is only meaningful when the things averaged are alike; hold crop (and ideally place/year) constant first.

Question 24

Using the Canada Field Crops dataset.

Compare Barley and Soybeans yields (all provinces, all years).

(a) Compute the mean and standard deviation of each crop.

(b) Compute the CV of each.

(c) State which crop is more variable in relative terms across the country, and cite the two CVs that justify your answer.

(d) Barley and soybeans have almost identical standard deviations, yet very different CVs. Explain how that is possible.

(e) A student says “barley and soybeans are equally variable because their standard deviations are the same.” Using your CV results, explain what is wrong with that claim.

Answer

• 1. Barley: mean ≈ 64.24, SD ≈ 9.10. Soybeans: mean ≈ 39.56, SD ≈ 9.08.
• 2. Barley CV ≈ 0.142; Soybeans CV ≈ 0.229.
• 3. Soybeans are more variable in relative terms — CV 0.229 vs 0.142.
• 4. The SDs are nearly equal (~9.1), but barley’s mean (64) is much higher than soybeans’ (40); dividing the same SD by a bigger mean gives a smaller CV. Relative variability depends on the mean, not the SD alone.
• 5. Equal SDs describe equal absolute spread, but relative to their different averages soybeans vary much more (CV 0.23 vs 0.14). “Equally variable” ignores that the two crops have very different mean yields.

Question 25

Using the Canada Field Crops dataset.

Filter to Canola and to a single year, Year = 2021.

(a) Compute the mean and median canola yield across provinces that year.

(b) The all-years average canola yield across provinces is about 41 bu/ac. Is the 2021 mean you just computed above or below that long-run average?

(c) Explain how a single bad year shows up when you compare a one-year mean to the multi-year mean.

(d) Explain why the provincial spread in a drought year can be smaller here than in the Saskatchewan RM data, even for the same drought (think about how much averaging StatsCan does at the province level).

(e) State, in one sentence, why province-level averages hide the RM-level variation that the Saskatchewan dataset reveals.

Answer

• 1. 2021 provincial canola mean/median — below the long-run figure.
• 2. 2021 is below the all-years mean (~41) — a drought year drags it down.
• 3. The one-year mean sits noticeably under the multi-year mean, flagging an unusual year.
• 4. StatsCan reports one number per province — an average over all its RMs — so within-province drought variation is already smoothed away.
• 5. Averaging up to the province hides the RM-to-RM spread that the finer Saskatchewan data still contains.

Question 26

Using the Canada Field Crops dataset.

Filter to Ontario (all crops, all years) and then to Saskatchewan.

(a) For Canola only, compute each province’s mean yield across all years.

(b) State which province has the higher average canola yield per acre, and by roughly how much.

(c) Explain why “higher yield per acre” does not mean “grows more canola” — and what other column you would need to judge total output.

(d) Compute (or describe how to compute) each province’s typical seeded acres for canola, and explain how that changes the picture.

(e) State the one-sentence distinction between a rate (yield per acre) and a total (production).

Answer

• 1. Ontario canola mean ≈ 46.7; Saskatchewan ≈ 39.1 bu/ac.
• 2. Ontario is higher by ~7–8 bu/ac per acre.
• 3. Yield is per-acre performance; total output also needs Seeded_acres — SK’s vast acreage means it produces far more canola overall.
• 4. SK canola seeded acres run to ~12 million+; Ontario’s are a small fraction — so SK dominates total production despite lower yield.
• 5. A rate (bu/ac) measures efficiency per unit land; a total (production) multiplies rate by acres — they can rank provinces oppositely.

Question 27

Using the Canada Field Crops dataset.

Filter to Oats (all provinces, all years).

(a) Compute the mean, median, and CV of oat yield.

(b) Compute Q1, Q3, and IQR.

(c) Compare the shape (mean vs median) to what you saw for oats in the Saskatchewan RM data. Are they similar in direction of skew?

(d) Explain why the provincial oat data has fewer, smoother values than the RM oat data (hundreds of RMs vs ten provinces).

(e) State, in one sentence, a trade-off between using fine-grained (RM) data and coarse (province) data for describing a crop.

Answer

• 1. mean ≈ 79.41, median ≈ 75.90, CV ≈ 0.193 bu/ac.
• 2. Q1 ≈ 68.60, Q3 ≈ 90.80, IQR ≈ 22.20.
• 3. Here mean (79.4) is above median (75.9) → a slight right/high skew, the opposite direction from the slightly left-skewed SK RM oats — a good reminder that aggregation level can change the apparent shape.
• 4. Only ~10 provinces × 11 years, each already an average, so far fewer and smoother numbers than ~300 RMs × 36 years.
• 5. Fine data shows local variation but is noisier/patchier; coarse data is clean and comparable but hides within-province detail.

Question 28

Using the Canada Field Crops dataset.

Consider blank (missing) yields across the whole file.

(a) Count how many province-crop-year rows have a blank yield.

(b) Identify (by filtering/sorting) which crops the blanks concentrate in, and in which provinces.

(c) Explain why a blank here means something different from a blank in the Manitoba variety file (recall: MASC suppression vs. crop-not-grown).

(d) Explain why computing a crop’s national mean while blanks are present still gives a sensible answer in Excel.

(e) State, in one sentence, why you should never replace these blanks with 0 before averaging.

Answer

• 1. ≈ 66 blank-yield rows (snapshot).
• 2. Blanks cluster in crops not widely grown in the east — Mixed grains and Rye are the most common blanks — and in the eastern provinces (Newfoundland and Labrador, New Brunswick, PEI).
• 3. Here a blank = crop not meaningfully grown in that province/year; MB blanks = grown but suppressed for privacy.
• 4. Excel’s AVERAGE skips blanks, so a crop’s mean is taken over the provinces/years that actually reported it.
• 5. Replacing blanks with 0 would count “not grown” as “a real zero yield,” dragging the mean down falsely.

Question 29

Using the Canada Field Crops dataset.

Filter to Durum wheat (all provinces, all years).

(a) Compute the mean and CV of durum yield.

(b) Count how many provinces actually report durum (non-blank yield). Compare that to the number of provinces reporting canola, and state what the difference tells you about how broadly each average applies.

(c) Explain why a “national durum yield” computed from only a few provinces still describes those provinces accurately, even though it covers less of the country than the canola figure.

(d) The CV of provincial canola yields is about 0.13. Compare durum’s CV to that. State which crop’s provincial yields are steadier, citing the two CVs.

(e) State, in one sentence, why knowing how many provinces contribute to an average is part of interpreting that average honestly.

Answer

• 1. mean ≈ 40.61, CV ≈ 0.287 bu/ac.
• 2. About 5 provinces report durum, versus 8+ for canola — so the durum average summarises far fewer provinces, and applies to a narrower part of the country.
• 3. The durum figure is still an accurate average of the provinces that report it; it simply describes fewer provinces than the canola figure, not the whole country.
• 4. Durum CV (≈0.29) is much higher than canola’s (≈0.13) — canola is steadier across provinces.
• 5. An average over ~5 provinces means something different from one over 10; the contributor count sets how broadly the number applies.

Question 30

Using the Canada Field Crops dataset.

Filter to Barley and to Year = 2023 (barley is grown in nearly every province).

(a) Compute the mean and median yield across provinces that year.

(b) Compute the standard deviation and CV.

(c) Identify the highest- and lowest-yielding provinces for barley that year (by sorting).

(d) Explain what the gap between the top and bottom province tells you, and why that gap is a form of spread.

(e) State, in one sentence, how you would describe barley’s 2023 national picture to a reader using one centre statistic and one spread statistic.

Answer

• 1. mean ≈ 58.68, median ≈ 55.20 bu/ac (across 9 reporting provinces).
• 2. SD ≈ 8.98, CV ≈ 0.153.
• 3. Highest: Manitoba (≈75.4); lowest: Quebec (≈46.1) bu/ac.
• 4. The top-minus-bottom gap (≈29 bu/ac) is the range across provinces — a spread measure showing how unevenly barley yielded nationally that year.
• 5. e.g. “In 2023, barley averaged about 59 bu/ac across provinces (median 55), with a CV of 0.15 indicating fairly modest variation from province to province.”

---

Section 2 — Conditional Functions & Lookups

Questions 31–40 · Saskatchewan RM Crop Yields

Question 31

Using the Saskatchewan RM Crop Yields dataset.

Use the full (all-years) dataset.

(a) Use AVERAGEIF to find the average canola yield in each of 2010, 2019, and 2021.

(b) Rank those three years by average yield, and identify which one stands out as unusually low.

(c) For each of the same three years, describe what you would check across the other crop columns to decide whether the low year was a broad event affecting all crops, rather than a canola-only result. (You do not need outside knowledge — describe the data check.)

(d) Your AVERAGEIF for 2023 returns a number even though several RMs have a blank canola cell that year. Explain what AVERAGEIF did with those blanks.

(e) Explain why filling those blanks with 0 before averaging would give a wrong answer.

Answer

• 1. 2010 ≈ 29.82, 2019 ≈ 40.78, 2021 ≈ 21.86 bu/ac. =AVERAGEIF(A:A, 2021, E:E).
• 2. 2019 highest, 2010 middle, 2021 lowest — the standout.
• 3. Compute each other crop’s AVERAGEIF for 2021 and see whether they are also low; if every crop’s 2021 average is depressed, the low year is broad, not canola-specific.
• 4. AVERAGEIF skips blank cells, averaging only the RMs that reported.
• 5. Filling blanks with 0 counts “no data” as “a yield of zero,” dragging the average down artificially.

Question 32

Using the Saskatchewan RM Crop Yields dataset.

(a) Use COUNTIF to count how many RM-year rows have a canola yield above 40 bu/ac across all years.

(b) Use COUNT to find how many non-blank canola cells there are in total.

(c) Divide the (a) count by the (b) count. Explain in plain language what that proportion represents.

(d) Repeat (a) with a threshold of 50 bu/ac instead of 40. Explain why the count drops so sharply, referring to the shape/spread of canola yields.

(e) Explain why the proportion in (c) describes reported RM-years only, and why that is a limitation if some years have many blanks.

Answer

• 1. ≈ 1483 rows. =COUNTIF(E:E, ">40").
• 2. ≈ 10,039 non-blank canola cells. =COUNT(E:E).
• 3. ≈ 1483 / 10039 ≈ 0.148 — about 15% of reported RM-years had canola above 40 bu/ac.
• 4. Above 50 the count falls to ≈181 — most canola yields sit well below 50, so raising the bar excludes the bulk of the distribution.
• 5. It is computed only over cells that have data; years with many blank RMs contribute fewer rows, so the proportion reflects reported RM-years, not all RM-years.

Question 33

Using the Saskatchewan RM Crop Yields dataset.

(a) Use a two-criteria lookup (XLOOKUP on a combined RM-and-Year key, or INDEX/MATCH) to pull the canola yield for RM 1 in 2023.

(b) Do the same to pull the spring wheat yield for RM 100 in 2023.

(c) Explain why a plain VLOOKUP on RM number alone would return a misleading result for this dataset.

(d) Suppose your two-criteria lookup returns an error for some RM-year you try. Give two distinct data reasons (not formula typos) this could happen.

(e) Explain, in one sentence, why matching on both RM and Year is essential in a dataset that has one row per RM per year.

Answer

• 1. ≈ 36.8 bu/ac. =XLOOKUP(1&"|"&2023, B:B&"|"&A:A, E:E).
• 2. ≈ 52.9 bu/ac.
• 3. RM 1 appears in ~36 years; VLOOKUP on RM alone returns the first match (one arbitrary year), not 2023.
• 4. The RM-year combination may not exist in the data, or that crop’s cell for that RM-year may be blank (crop not grown/reported).
• 5. Each RM appears once per year, so only RM and Year together identify a single row.

Question 34

Using the Saskatchewan RM Crop Yields dataset.

(a) Use COUNTIFS to count how many RM-year rows have canola above 40 AND spring wheat above 45 in the same row.

(b) Use COUNTIF to count rows with canola above 40 (ignoring wheat).

(c) Explain why the count in (a) is smaller than the count in (b).

(d) Explain what kind of question COUNTIFS (with two conditions on the same row) answers that two separate COUNTIFs cannot.

(e) State, in one sentence, the difference between “rows meeting condition A” and “rows meeting A and B together.”

Answer

• 1. ≈ 1204 rows. =COUNTIFS(E:E, ">40", C:C, ">45").
• 2. ≈ 1483 rows.
• 3. 1. requires both conditions in the same RM-year, a stricter test, so it is a subset of the rows meeting canola > 40 alone.
• 4. COUNTIFS counts rows where conditions co-occur in the same record; separate COUNTIFs only count each condition independently and cannot tell you how often they happen together.
• 5. “A alone” counts any row passing A; “A and B” counts only rows passing both at once — always fewer or equal.

Question 35

Using the Saskatchewan RM Crop Yields dataset.

(a) Use AVERAGEIF on the RM column to compute the average barley yield across all years in RM 1, and in RM 100.

(b) State which RM has the higher long-run average barley yield, and by roughly how much.

(c) Use COUNT (via a helper or COUNTIF on the RM) to find how many years each RM reports barley. Explain why comparing the two averages is fair only if both rest on a similar number of years.

(d) Explain why an AVERAGEIF on RM gives a per-RM average, whereas an AVERAGEIF on Year gives a per-year average — and why you must be clear which you are reporting.

(e) State, in one sentence, what “average barley yield in RM 1” means in words.

Answer

• 1. RM 1 ≈ 56.56 bu/ac; RM 100 ≈ 43.89 bu/ac. =AVERAGEIF(B:B, 1, F:F).
• 2. RM 1 is higher by ≈ 12.7 bu/ac.
• 3. Each reports ~35 years, so the comparison is fair; if one had far fewer years it could reflect a different set of (e.g. good or bad) years.
• 4. AVERAGEIF on RM averages all years for one RM; on Year it averages all RMs for one year — different groupings answering different questions.
• 5. It is the mean barley yield in RM 1 taken over all the years that RM reported barley.

Question 36

Using the Saskatchewan RM Crop Yields dataset.

(a) Use COUNTIF with a computed threshold to count how many canola cells (all years) are above the overall average canola yield. First compute the overall average with AVERAGE, then use ">"& that average as the COUNTIF criterion.

(b) Report both the overall average and the count above it.

(c) Roughly what fraction of reported canola cells are above the mean? Explain whether you would expect this fraction to be exactly one-half.

(d) Explain why, for a left-skewed distribution, more than half the values can sit above the mean.

(e) Explain why you cannot write the criterion as ">AVERAGE(E:E)" in quotes, and must use ">"&AVERAGE(E:E) instead.

Answer

• 1. =COUNTIF(E:E, ">"&AVERAGE(E:E)).
• 2. overall average ≈ 28.29 bu/ac; count above ≈ 4519.
• 3. ≈ 4519 / 10039 ≈ 0.45 — a bit below half, not exactly one-half.
• 4. In a left-skewed set the low tail pulls the mean down, so a majority can sit above it; here the pooled multi-decade data behaves differently, and the fraction lands near 0.45 — the point is that “above the mean” need not be 50%.
• 5. Quoted, ">AVERAGE(E:E)" is treated as literal text; ">"&AVERAGE(E:E) concatenates the “>” with the computed number to build a real numeric criterion.

Question 37

Using the Saskatchewan RM Crop Yields dataset.

(a) Use AVERAGEIF to compute the average canola yield for 2015 and for 2019.

(b) Use AVERAGEIF to compute the average barley yield for the same two years.

(c) Compute the change (later minus earlier) for each crop. State which crop rose more between 2015 and 2019 in absolute bu/ac.

(d) Explain why comparing absolute changes between two crops with different typical yields can be misleading, and what you could divide by to compare fairly.

(e) State, in one sentence, the difference between an absolute change (bu/ac) and a relative change (percent).

Answer

• 1. Canola: 2015 ≈ 36.06, 2019 ≈ 40.78 bu/ac.
• 2. Barley: 2015 ≈ 55.25, 2019 ≈ 68.59 bu/ac.
• 3. Canola rose ≈ 4.7; barley rose ≈ 13.3 — barley rose more in absolute bu/ac.
• 4. Barley yields more to begin with, so a larger absolute rise is partly scale; divide the change by the starting value to get a percent change for a fair comparison.
• 5. Absolute change is the raw bu/ac difference; relative change expresses that difference as a fraction of the starting value.

Question 38

Using the Saskatchewan RM Crop Yields dataset.

(a) Write an IF (or IFS) formula that labels each RM-year’s canola yield as "Low" (below 20), "Medium" (20 to 40), or "High" (above 40), leaving blanks blank.

(b) Use COUNTIF to count how many cells fall in each of the three labels.

(c) Which label is most common? Relate that to the overall average canola yield (~28 bu/ac).

(d) Explain how you would modify the formula to avoid labelling a blank cell as “Low.”

(e) State, in one sentence, why an explicit label column can be more useful for a reader than the raw numbers alone.

Answer

• 1. =IF(E2="", "", IFS(E2<20,"Low",E2<=40,"Medium",TRUE,"High")).
• 2. Count each label with =COUNTIF(label_range, "Medium"), etc.
• 3. “Medium” (20–40) is most common, consistent with the ~28 bu/ac average sitting inside that band.
• 4. Wrap the classification in IF(E2="", "", …) so blank inputs return blank, not “Low.”
• 5. Labels group the data into meaningful bands a reader can scan quickly, without interpreting every raw number.

Question 39

Using the Saskatchewan RM Crop Yields dataset.

(a) Use COUNTIF to count non-blank canola cells and non-blank flax cells across all years (COUNT on each column).

(b) State which crop is reported in more RM-years, and by roughly how much.

(c) Explain what a difference in the number of reported cells tells you about how commonly each crop is recorded across RMs and years — using only the counts, not outside knowledge.

(d) Explain why you would use COUNT (numbers only) rather than COUNTA (any non-empty cell) here.

(e) State, in one sentence, why the count of reported cells is itself a useful summary, separate from the average yield.

Answer

• 1. Canola ≈ 10,039; Flax ≈ 8,077 non-blank cells. =COUNT(E:E), =COUNT(J:J).
• 2. Canola is reported in more RM-years, by ≈ 1,960.
• 3. Canola has more reported cells, so it is recorded in more RM-years than flax across the 1990–2025 span.
• 4. COUNT counts numeric entries only; COUNTA would also count any stray text, giving a misleading total.
• 5. It measures data coverage — how widely the crop is recorded — which the average yield does not capture.

Question 40

Using the Saskatchewan RM Crop Yields dataset.

(a) Use AVERAGEIF to compute the average canola yield for the year 2021 and for 2023.

(b) Use COUNTIFS to count, within 2021 only, how many RMs had canola below 20 bu/ac.

(c) Use COUNTIFS to do the same for 2023.

(d) Compare the two counts and explain what the difference says about how the two years differed at the low end.

(e) Explain, in one sentence, why comparing counts below a fixed threshold across two years is a fair way to describe how bad the worse year was.

Answer

• 1. 2021 ≈ 21.86; 2023 ≈ 33.88 bu/ac. =AVERAGEIF(A:A, 2021, E:E).
• 2. 2021 below 20: =COUNTIFS(A:A, 2021, E:E, "<20") — a large count (roughly a third of RMs).
• 3. 2023 below 20: far fewer RMs.
• 4. Many more RMs fell below 20 bu/ac in 2021 than 2023, showing 2021’s low end was far worse — a broad poor year.
• 5. A fixed threshold gives a common yardstick, so the count of RMs beneath it directly compares how many did badly in each year.

Questions 41–50 · Manitoba Wheat Variety

Question 41

Using the Manitoba Wheat Variety dataset.

(a) Use COUNTIF on the Reported column to count the suppressed rows (FALSE) and the reported rows (TRUE).

(b) State which is larger.

(c) Divide the reported count by the total number of rows. Explain in plain language what that proportion represents.

(d) Explain what a suppressed row means in this dataset, and why it is a different kind of “missing” than a blank in the Saskatchewan file. (Describe the data meaning, not farming.)

(e) A student deletes all suppressed rows before analysing. Give one calculation that is unaffected by this, and one conclusion that would be biased by it.

Answer

• 1. ≈ 2960 FALSE, ≈ 2398 TRUE. =COUNTIF(G:G, "FALSE").
• 2. Suppressed (FALSE) is larger.
• 3. ≈ 2398 / 5358 ≈ 0.45 — about 45% of rows carry a published yield.
• 4. A suppressed row means the number was withheld (too few farms reported); a Saskatchewan blank means the crop was not grown/reported. One withholds a known value, the other has no value.
• 5. Unaffected: the average of reported yields (blanks are skipped anyway). Biased: any statement about how widely a variety is grown, since suppressed rows carry that coverage information.

Question 42

Using the Manitoba Wheat Variety dataset.

(a) Use AVERAGEIF to compute the average reported yield for the variety AAC BRANDON (BW 932).

(b) Use COUNTIF to count how many reported rows that variety has.

(c) Use COUNTIFS to count how many rows for that variety are suppressed (Variety = it AND Reported = FALSE).

(d) Add the reported and suppressed counts. Explain what that total represents for the variety.

(e) Explain why the average from (a) rests only on the reported rows, and what the suppressed count in (c) adds to your understanding that the average alone does not.

Answer

• 1. ≈ 59.62 bu/ac. =AVERAGEIF(D:D, "AAC BRANDON (BW 932)", F:F).
• 2. ≈ 519 reported rows.
• 3. ≈ 51 suppressed rows. =COUNTIFS(D:D, "AAC BRANDON (BW 932)", G:G, "FALSE").
• 4. ≈ 570 total appearances — every municipality-year where the variety was listed.
• 5. The average uses only the 519 reported values; the 51 suppressed rows show additional places it appeared but where yields were withheld — coverage the average cannot show.

Question 43

Using the Manitoba Wheat Variety dataset.

Consider the variety GLENN, which is reported in very few municipality-years.

(a) Use COUNTIFS to count how many GLENN rows are reported (TRUE) and how many are suppressed (FALSE).

(b) State what fraction of GLENN’s appearances have a published yield.

(c) Explain why any average yield you compute for GLENN should be read with caution, using the counts.

(d) Contrast this with a widely-grown variety like AAC BRANDON (BW 932), which has roughly 519 reported results, in terms of how much data backs each variety’s average.

(e) State, in one sentence, why the number of reported values matters when comparing two varieties’ averages.

Answer

• 1. GLENN: ≈ 4 reported, ≈ 96 suppressed. =COUNTIFS(D:D, "GLENN", G:G, "FALSE").
• 2. Only ≈ 4 / 100 ≈ 4% of its appearances have a published yield.
• 3. An average from ~4 values is fragile — a single unusual result would swing it heavily.
• 4. AAC Brandon’s average rests on ~519 values, GLENN’s on ~4 — vastly different reliability.
• 5. An average over many values is far more stable and representative than one over a handful.

Question 44

Using the Manitoba Wheat Variety dataset.

(a) Use COUNTIF to count how many reported yields exceed 70 bu/ac.

(b) Use COUNTIF to count how many exceed 80 bu/ac.

(c) Divide each count by the total reported count. Explain what the two proportions tell you about the top end of the distribution.

(d) Explain why the count drops steeply from the 70 threshold to the 80 threshold, referring to where most yields sit.

(e) State, in one sentence, how counting above successive thresholds can sketch the shape of a distribution’s upper tail.

Answer

• 1. ≈ 569 reported yields above 70. =COUNTIF(F:F, ">70").
• 2. ≈ 97 above 80.
• 3. ≈ 569 / 2398 ≈ 0.24 above 70; ≈ 97 / 2398 ≈ 0.04 above 80 — roughly a quarter clear 70, but only ~4% clear 80.
• 4. Most yields cluster in the 50s–60s, so few reach the high 70s and very few exceed 80; the tail thins quickly.
• 5. Successive threshold counts show how fast the number of values falls off toward the high end — a numeric picture of the upper tail.

Question 45

Using the Manitoba Wheat Variety dataset.

(a) Use AVERAGEIF to compute the average reported yield for Year = 2021 and for Year = 2023.

(b) Use COUNTIF to count the reported rows in each year.

(c) State which year had both the lower average and describe how its reported count compares.

(d) Explain why comparing two years’ average yields is only fair when a similar set of varieties is reported in each — and how the counts help you check that.

(e) State, in one sentence, one thing the average yield hides that the count of reported rows reveals.

Answer

• 1. 2021 ≈ 49.64; 2023 ≈ 61.42 bu/ac. =AVERAGEIF(A:A, 2021, F:F).
• 2. Reported counts: 2021 ≈ 396, 2023 ≈ 423.
• 3. 2021 had the lower average and a slightly lower reported count.
• 4. If the varieties tested differ between years, an average gap could reflect the mix, not the year; similar counts and overlapping varieties make the comparison fairer.
• 5. The average hides how many results back it; the count shows how much data supports each year’s figure.

Question 46

Using the Manitoba Wheat Variety dataset.

(a) Use a lookup (XLOOKUP or INDEX/MATCH) to find the reported yield for AAC BRANDON (BW 932) in the municipality ALONSA in 2020.

(b) Try the same lookup for a variety-municipality-year you expect to be suppressed (Reported = FALSE). Look carefully at the value your lookup returns for that blank Yield_bu_ac cell.

(c) When XLOOKUP (or INDEX/MATCH) points at an empty cell, Excel returns 0, not a blank. Explain why that behaviour is dangerous here, given that a suppressed yield is not a real zero.

(d) Explain why you would check the Reported column before trusting a looked-up yield of 0.

(e) State, in one sentence, why a looked-up 0 from a suppressed row should not be read as “a yield of zero.”

Answer

• 1. ≈ 65.1 bu/ac.
• 2. The lookup finds the suppressed row, but because its Yield_bu_ac cell is empty, Excel returns 0 — a real trap.
• 3. A returned 0 looks like a measured yield of zero, but the row is suppressed (withheld), so treating that 0 as data would drag any average down falsely.
• 4. The Reported flag tells you the 0 came from a withheld cell, not a real measurement, so you know to exclude it.
• 5. The 0 is an artifact of looking up an empty cell — the yield was withheld, not measured as zero.

Question 47

Using the Manitoba Wheat Variety dataset.

(a) Use AVERAGEIF to compute the average reported yield for SY MANNESS and for AAC BRANDON (BW 932).

(b) Use COUNTIF to count the reported rows behind each average.

(c) State which variety has the higher average, and which has more reported data.

(d) Explain why the higher-average variety is not automatically the “better documented” one, using your two counts.

(e) State, in one sentence, why you would report both the average and the count when comparing varieties.

Answer

• 1. SY MANNESS ≈ 72.12; AAC Brandon ≈ 59.62 bu/ac.
• 2. SY MANNESS ≈ 108 reported; AAC Brandon ≈ 519 reported.
• 3. SY MANNESS has the higher average; AAC Brandon has far more reported data.
• 4. The higher average (SY MANNESS) rests on ~108 values, while AAC Brandon’s lower average rests on ~519 — higher average ≠ more evidence.
• 5. The average states the level; the count states how much data supports it — both are needed to compare fairly.

Question 48

Using the Manitoba Wheat Variety dataset.

(a) Use COUNTIFS to count reported rows for AAC BRANDON (BW 932) in each year 2020 through 2025 (Variety = it AND Year = each).

(b) State which year has the most reported AAC BRANDON results.

(c) Explain what a change in the yearly reported count tells you about how the variety’s presence in the data changed over time — using counts only.

(d) Explain why you would divide by the yearly count if you wanted to compare this variety’s average yield across years fairly.

(e) State, in one sentence, the difference between “how many places grew it” (count) and “how well it yielded” (average).

Answer

• 1. =COUNTIFS(D:D, "AAC BRANDON (BW 932)", A:A, 2020), etc. — counts near 80–95 per year.
• 2. The year with the highest count (around 2020) has the most reported results.
• 3. A rising or falling yearly count shows the variety being reported in more or fewer municipalities over time.
• 4. The average already divides by the count within each year, so each year’s mean is comparable regardless of how many municipalities reported.
• 5. The count measures how widely it appears in the data; the average measures its yield where reported — two separate facts.

Question 49

Using the Manitoba Wheat Variety dataset.

(a) Use COUNTIFS to count how many reported rows in 2023 had a yield above 70 bu/ac.

(b) Use COUNTIF to count all reported rows in 2023.

(c) Compute the proportion above 70 for 2023. Explain what it tells you about that year’s top end.

(d) Explain why you must use COUNTIFS (two conditions: year and threshold) rather than a single COUNTIF here.

(e) State, in one sentence, why a proportion (share above a threshold) can be easier to compare across years than a raw count.

Answer

• 1. =COUNTIFS(A:A, 2023, F:F, ">70") — a moderate count.
• 2. ≈ 423 reported rows in 2023.
• 3. The share above 70 shows what fraction of 2023 results were strong; compare it to other years to see if 2023’s top end was unusually high or low.
• 4. You need both the year condition and the threshold condition on the same row; a single COUNTIF can only apply one.
• 5. A proportion adjusts for how many rows each year has, so years with different counts can be compared on equal footing.

Question 50

Using the Manitoba Wheat Variety dataset.

(a) Use COUNTIF to count the total reported rows (Reported = TRUE) and the suppressed rows (Reported = FALSE) in the dataset.

(b) Use COUNTIFS to count reported rows for a single common variety across all years (e.g. AAC STARBUCK <SECAN>).

(c) Divide the variety’s reported count by the total reported count. Explain what that share represents.

(d) Explain why this share describes the variety’s presence among reported results, not its true share of all wheat acres.

(e) State, in one sentence, the limitation of using a variety-trial dataset to judge how popular a variety is overall.

Answer

• 1. ≈ 2398 reported, ≈ 2960 suppressed.
• 2. AAC STARBUCK <SECAN> reported ≈ 374. =COUNTIFS(D:D, "AAC STARBUCK <SECAN>", G:G, "TRUE").
• 3. ≈ 374 / 2398 ≈ 0.16 — the variety makes up about 16% of reported results.
• 4. It counts reported trial results, not planted acres; suppressed and unreported places are excluded, so it is not a true popularity share.
• 5. The dataset records reported trial results, so it can undercount or overcount a variety’s real-world use.

Questions 51–60 · Canada Field Crops

Question 51

Using the Canada Field Crops dataset.

(a) Use SUMIF to compute the total canola seeded acres across all provinces in 2023.

(b) Use SUMIF per province (or a sort) to identify which province contributes the most to that total, and roughly what share it makes up.

(c) State what that dominance implies about where the “national” canola seeded-acres figure really comes from.

(d) Explain why summing Seeded_acres across provinces is meaningful, but summing Yield_bu_ac across provinces is not.

(e) Describe, in one sentence, the correct way to get a national yield figure from provincial yields (in words — no new formula needed).

Answer

• 1. ≈ 22,085,300 acres. =SUMIF(C:C, "Canola", D:D) filtered to 2023 (or SUMIFS on Crop and Year).
• 2. Saskatchewan ≈ 12,400,400 acres — over half the national total.
• 3. National canola seeded-acres figures are largely a Saskatchewan story.
• 4. Acres are an extensive quantity and add up; yield is a rate (bu per acre), and adding rates across provinces is meaningless.
• 5. Weight each province’s yield by its acres (an acreage-weighted average) rather than a plain average.

Question 52

Using the Canada Field Crops dataset.

(a) Use SUMIFS to compute total seeded acres for canola, spring wheat, and barley in 2023 (three totals).

(b) Rank the three crops by seeded acres.

(c) Explain what “seeded acres” measures, and why it is a better measure of a crop’s scale than its average yield.

(d) Explain why a crop can rank high on seeded acres but not on yield per acre, using the idea of rate vs. total.

(e) State, in one sentence, why you would look at both seeded acres and yield to describe a crop’s importance.

Answer

• 1. 2023 seeded acres: canola ≈ 22,085,300; spring wheat ≈ 19,496,400; barley ≈ 7,330,600.
• 2. Canola > spring wheat > barley.
• 3. Seeded acres measures how much land is planted — the crop’s footprint; average yield is per-acre performance, not scale.
• 4. A high-acreage crop may have modest per-acre yield, and a high-yielding crop may be planted on little land — rate and total are separate.
• 5. Acres show how big the crop is; yield shows how productive it is per acre — together they describe importance.

Question 53

Using the Canada Field Crops dataset.

(a) Use AVERAGEIF to compute the average canola yield in Ontario (all years) and in Saskatchewan (all years).

(b) State which province has the higher average canola yield per acre, and by roughly how much.

(c) Use SUMIF (or a lookup) to compare their 2023 canola seeded acres.

(d) Explain why the province with the higher yield per acre is not necessarily the one that grows the most canola, using your two results.

(e) State, in one sentence, the difference between a rate (yield per acre) and a total (seeded acres or production).

Answer

• 1. Ontario ≈ 46.72; Saskatchewan ≈ 39.09 bu/ac.
• 2. Ontario is higher by ≈ 7.6 bu/ac per acre.
• 3. Saskatchewan seeds ≈ 12,400,400 canola acres in 2023 vs a small fraction of that in Ontario.
• 4. Ontario yields more per acre, but Saskatchewan plants vastly more acres, so SK grows far more canola overall.
• 5. A rate measures output per unit land; a total measures the overall amount — they can rank provinces oppositely.

Question 54

Using the Canada Field Crops dataset.

(a) Use a lookup (XLOOKUP or INDEX/MATCH) with a combined Province-Crop-Year key to find the canola yield in Manitoba in 2023.

(b) Use the same approach to find the canola seeded acres in Manitoba in 2023.

(c) Explain why you need three criteria (Province, Crop, Year) to identify a single row in this dataset.

(d) Suppose a lookup returns a blank yield for some Province-Crop-Year. Give one data reason (not a typo) this can happen.

(e) State, in one sentence, why matching on fewer than all three keys risks returning the wrong row.

Answer

• 1. ≈ 44.6 bu/ac.
• 2. ≈ 3,128,200 acres.
• 3. Each row is one Province-Crop-Year; only all three together single out a unique record.
• 4. That crop may not be grown/reported in that province-year, so the yield cell is empty — and a lookup pointing at an empty cell returns 0 in Excel, which must not be read as a real zero yield.
• 5. With fewer keys, several rows can match, and the lookup returns the first — possibly the wrong crop, province, or year.

Question 55

Using the Canada Field Crops dataset.

(a) Use COUNTIF/COUNTBLANK to count how many province-crop-year rows have a blank yield.

(b) Use COUNTIFS to count how many of those blank-yield rows are for one specific crop of your choice (e.g. Durum wheat).

(c) Explain what a blank means in this dataset, and how it differs from a suppressed value in the Manitoba variety file.

(d) Explain why computing a crop’s national mean while blanks are present still gives a sensible answer in Excel.

(e) State, in one sentence, why you should never replace these blanks with 0 before averaging.

Answer

• 1. ≈ 66 blank-yield rows (snapshot).
• 2. Filter blanks to one crop with COUNTIFS(Crop, "Durum wheat", Yield, "") (or count blanks within that crop) — durum blanks fall in the many provinces that do not grow it.
• 3. A blank here = crop not grown/reported in that province-year; a Manitoba suppression = grown but the number withheld for privacy.
• 4. Excel’s AVERAGE skips blanks, so the mean is taken over provinces/years that reported the crop.
• 5. Replacing blanks with 0 would count “not grown” as “a real zero yield,” dragging the mean down falsely.

Question 56

Using the Canada Field Crops dataset.

(a) Use COUNTIF to count how many spring wheat rows (all provinces, all years) have a yield above 55 bu/ac.

(b) Use COUNT to find the total number of non-blank spring wheat yields.

(c) Compute the proportion above 55 and explain what it tells you about how often provinces reached that level.

(d) Explain why this proportion mixes provinces and years together, and why that limits what it can tell you about any single province.

(e) Describe, in one sentence, how you could refine the count to describe just one province.

Answer

• 1. ≈ 30 rows above 55. =COUNTIF(E:E, ">55") on spring wheat rows.
• 2. ≈ 94 non-blank spring wheat yields.
• 3. ≈ 30 / 94 ≈ 0.32 — about a third of province-years reached above 55 bu/ac.
• 4. It pools all provinces and years, so it describes the whole set, not any one province’s typical performance.
• 5. Use COUNTIFS adding a Province condition to count above 55 within a single province.

Question 57

Using the Canada Field Crops dataset.

(a) Use AVERAGEIF to compute the average barley yield in 2023 across provinces, and the average soybean yield in 2023.

(b) State which crop yielded more per acre on average that year.

(c) Use COUNTIF to count how many provinces reported each crop in 2023.

(d) Explain why comparing the two averages requires noting how many provinces contribute to each — using your counts.

(e) State, in one sentence, why an average over more provinces is a broader statement than one over fewer.

Answer

• 1. Compute each with AVERAGEIFS (Crop and Year). Barley ≈ high 50s; soybeans lower (report your values).
• 2. Barley yields more per acre on average in 2023.
• 3. Barley is reported by more provinces than soybeans (soybeans concentrate in fewer).
• 4. Barley’s average spans more provinces; soybeans’ spans fewer, so the two averages describe different breadths of the country.
• 5. An average over more provinces summarises a larger share of the country than one over a few.

Question 58

Using the Canada Field Crops dataset.

(a) Use SUMIFS to compute total canola seeded acres for 2020 and for 2023 (two totals).

(b) Compute the change (2023 minus 2020) in total seeded acres.

(c) Explain what an increase or decrease in national seeded acres tells you about the crop’s footprint over those years — using the totals only.

(d) Explain why comparing seeded acres across years is valid, whereas comparing summed yields across years would not be.

(e) State, in one sentence, why seeded acres is a meaningful quantity to add up but yield is not.

Answer

• 1. Compute with SUMIFS(Seeded, Crop, "Canola", Year, 2020) and for 2023 (≈22.1M in 2023).
• 2. Report the difference (2023 total minus 2020 total).
• 3. A rise means more land was planted to canola nationally; a fall means less — a footprint change.
• 4. Acres add up across provinces and years; yields are per-acre rates and summing them has no physical meaning.
• 5. Acres are an amount of land (extensive); yield is output per acre (a rate), which must be averaged, not summed.

Question 59

Using the Canada Field Crops dataset.

(a) Use AVERAGEIF to compute the average canola yield for each of 2019, 2021, and 2023 across provinces.

(b) Rank the three years and identify the lowest.

(c) Explain how comparing a single year’s average to the others flags an unusual year — using the numbers only.

(d) Explain why the province-level average in the low year may not look as extreme as the RM-level average for the same year in the Saskatchewan data.

(e) State, in one sentence, why averaging up to the province level hides some of the variation the finer data shows.

Answer

• 1. Compute each with AVERAGEIFS (Crop = Canola, Year). 2021 is the low one.
• 2. 2021 lowest; 2019 and 2023 higher.
• 3. The low year’s average sits clearly below the others, marking it as unusual without any outside information.
• 4. Each province figure is already an average over its farms, so within-province lows are smoothed before you see them.
• 5. Province averages combine many farms into one number, hiding the farm- or RM-level spread beneath them.

Question 60

Using the Canada Field Crops dataset.

(a) Use SUMIFS to compute total spring wheat seeded acres in 2023, and use AVERAGEIFS to compute the average spring wheat yield in 2023.

(b) Explain, in words, why you cannot multiply your single average yield by your single total acreage to get exact national production.

(c) Explain what extra breakdown (by province) you would need to compute production correctly.

(d) Explain why a province with high yield but few acres contributes little to national production.

(e) State, in one sentence, the general relationship between a rate, a quantity, and a total (production = yield × acres, per province).

Answer

• 1. Total seeded ≈ 19,496,400 acres; average yield computed with AVERAGEIFS.
• 2. The national average yield is a plain average across provinces, not weighted by acres, so average × total does not equal the true sum of province-level (yield × acres).
• 3. You need each province’s own yield and acres, multiply within each province, then add those products.
• 4. Production = yield × acres per province; few acres means a small product regardless of a high yield.
• 5. For each province, production = yield × acres; national production is the sum of those province products, not the national average times national acres.

---

Section 3 — Charts

Charts are marked partly on presentation: every chart needs a descriptive title, labelled axes, and clean formatting.

Questions 61–70 · Saskatchewan RM Crop Yields

Question 61

Using the Saskatchewan RM Crop Yields dataset.

Filter to Year = 2023, Canola column.

(a) Build a histogram of 2023 canola yields (about 10–15 bins), with a descriptive title and labelled axes.

(b) Describe the shape of the distribution in Module-1 terms (symmetric, or skewed — and in which direction).

(c) Point to the specific feature of your histogram that supports the shape you described.

(d) Based on that shape, would you summarise the centre with the mean or the median? Justify it.

(e) Explain one change to the bin width that would make the shape harder to read, and why.

Answer

• 1. Histogram; title e.g. “Distribution of Canola Yields Across Saskatchewan RMs, 2023”; x = “Yield (bu/ac)”, y = “Number of RMs”.
• 2. Left/low-skewed (mean 33.9 < median 35.5).
• 3. The long thin tail toward the low single digits, with the bulk in the 30s–40s.
• 4. The median, because the low tail pulls the mean below the typical value.
• 5. Too few bins hides the tail (looks symmetric); too many bins turns it into noise — either obscures the shape.

Question 62

Using the Saskatchewan RM Crop Yields dataset.

(a) Build a single box-and-whisker chart comparing Canola, Spring Wheat, and Barley yields in 2023 (three boxes), titled with labelled axes.

(b) Identify which crop has the widest box.

(c) Explain what the width of the box represents, and restate it in plain language about that crop’s yields.

(d) Explain how the chart marks outliers, and what the whiskers represent.

(e) For 2023, none of the three crops shows flagged outliers even though some RMs had very low yields. Explain how a wide box can prevent low values from being flagged.

Answer

• 1. Box-and-whisker, three crops, titled/labelled.
• 2. Barley (IQR ≈ 41.4 vs canola 22.3, wheat 27.1).
• 3. Box width = the interquartile range, the spread of the middle 50% of RMs; barley yields varied most across RMs.
• 4. Outliers appear as individual dots beyond the whiskers (1.5×IQR rule); the whiskers reach the most extreme non-outlier values.
• 5. A wide IQR makes 1.5×IQR large, pushing the lower fence far below any real value (barley’s ≈ −29.5), so even a 2.9 bu/ac RM is inside the whisker.

Question 63

Using the Saskatchewan RM Crop Yields dataset.

(a) Build a PivotTable of average canola yield by year, then create a line chart of average canola yield from 1990 to 2025, titled with labelled axes.

(b) Describe the overall direction of the line from the early 1990s to the mid-2020s.

(c) Identify the sharpest single-year dip on the chart and give its approximate year.

(d) Explain how the chart lets you separate the long-run direction from one-off dips.

(e) Explain why a line chart is the right tool for showing this year-by-year story, and a pie chart is not.

Answer

• 1. Line chart; x = Year, y = “Average yield (bu/ac)”.
• 2. A clear upward direction — from ~19 bu/ac in 1995 to the low-to-mid 40s by 2025.
• 3. The sharpest single-year drop is into 2021 (from ~38 bu/ac in 2020 down to ~22). (Note: the lowest year overall is actually 2002 at ~16 bu/ac; here the question asks for the sharpest one-year drop, which is 2021.)
• 4. The rising slope over many years is the long-run direction; a single deep notch (2021) that recovers afterward is a one-off dip.
• 5. A line chart shows change over ordered time; a pie chart shows parts of a whole at one instant and cannot show a trend.

Question 64

Using the Saskatchewan RM Crop Yields dataset.

(a) Build one line chart with two lines — average canola yield by year and average spring wheat yield by year — from 1990 to 2025, titled with labelled axes and a legend.

(b) Describe whether the two lines tend to rise and fall together or move independently.

(c) Identify one year where both lines dip at the same time.

(d) Explain what a legend adds to this chart and why it is required for a reader to interpret it.

(e) Explain why plotting the two crops on the same axes (both bu/ac) makes the comparison fair, and when it would not be.

Answer

• 1. Two-line chart with a legend.
• 2. The two lines move together — good and poor years largely coincide.
• 3. 2021 — both canola and spring wheat dip sharply.
• 4. The legend identifies which line is which crop; without it the two series are indistinguishable.
• 5. Both are bu/ac, so a shared axis compares like with like; it would be unfair if the crops used different units (e.g. bu/ac vs lb/ac).

Question 65

Using the Saskatchewan RM Crop Yields dataset.

(a) Filter to Year = 2023 and build a histogram of the Barley column (10–15 bins), titled with labelled axes.

(b) Describe the shape in Module-1 terms.

(c) The 2023 canola yields have an IQR of about 22 bu/ac. Compare the width of this barley histogram’s spread to that. Which crop’s yields are more spread out in 2023?

(d) Explain what feature of the histogram lets you judge spread by eye.

(e) Explain why two histograms should use the same axis scale if you want to compare their spreads visually.

Answer

• 1. Histogram, titled/labelled.
• 2. Slightly left-skewed (mean 54.6 < median 59.9).
• 3. Barley is more spread out (IQR ≈ 41.4 vs canola’s ≈ 22.3) — its bars stretch across a wider range.
• 4. The horizontal extent of the bars (how far the data spreads along the x-axis) shows the spread.
• 5. Different x-axis scales distort the visual width; a shared scale makes the spreads directly comparable.

Question 66

Using the Saskatchewan RM Crop Yields dataset.

(a) Build a box plot comparing Canola yields across three years — 2019, 2021, and 2023 — (three boxes), titled with labelled axes.

(b) Identify which year has the lowest median, and which has the lowest box overall.

(c) Describe how the 2021 box differs from the 2019 box in both position (height) and spread (width).

(d) Explain what the box plot shows about 2021 that a single average yield for 2021 would not.

(e) Explain, in one sentence, why comparing full distributions (box plots) across years is more informative than comparing means alone.

Answer

• 1. Box plot, three years, titled/labelled.
• 2. 2021 has the lowest median and lowest box.
• 3. The 2021 box sits much lower (median ~23 vs ~42 in 2019) and its spread relative to its low centre is wide.
• 4. It shows the full range and quartiles — how low the worst RMs fell and how spread out 2021 was — not just the average.
• 5. Box plots reveal centre, spread, and extremes together, so you see how years differ, not only that their means differ.

Question 67

Using the Saskatchewan RM Crop Yields dataset.

(a) Filter to Year = 2023 and build a histogram of the Oats column, titled with labelled axes.

(b) Describe the shape in Module-1 terms (mean 71.1 vs median 75.9 will guide you).

(c) Explain which direction the tail points, and how the mean-vs-median relationship matches that.

(d) Explain why a reader glancing only at the tallest bars might misjudge the “typical” oat yield, and what statistic protects against that.

(e) Explain one presentation choice (title, axis label, or bin width) that would most improve a reader’s ability to read this histogram.

Answer

• 1. Histogram, titled/labelled.
• 2. Left/low-skewed (mean 71.1 < median 75.9).
• 3. The tail points toward the low end; the mean sits below the median, consistent with a low tail.
• 4. The tallest bars show where data concentrates, but a low tail drags the mean down; the median better marks the typical value.
• 5. Any well-justified choice — e.g. a clear y-axis label (“Number of RMs”) or a bin width that reveals the tail without noise.

Question 68

Using the Saskatchewan RM Crop Yields dataset.

(a) Build a box plot comparing Durum and Canola yields in 2021 (two boxes), titled with labelled axes.

(b) Identify which crop’s box sits lower and which is more spread out.

(c) Durum in 2021 has mean 21.7 above median 17.2. Describe what direction of skew that implies and how a box plot would show it (where the median line sits inside the box).

(d) Explain how the position of the median line within a box signals skew.

(e) Explain, in one sentence, why a box plot can reveal skew even without computing the mean.

Answer

• 1. Box plot, two crops, titled/labelled.
• 2. Report which sits lower/wider from your chart (durum 2021 is very low and wide).
• 3. Mean above median → right/high skew; the median line sits in the lower part of the box, closer to Q1.
• 4. A median line nearer Q1 (bottom) signals a longer upper half → right skew; nearer Q3 signals left skew.
• 5. The median’s off-centre position inside the box shows which half is stretched, revealing skew directly.

Question 69

Using the Saskatchewan RM Crop Yields dataset.

(a) Build a line chart of average Barley yield by year, 1990–2025, titled with labelled axes.

(b) Describe the overall direction and identify the year with the sharpest single-year drop.

(c) Canola’s sharpest single-year drop over 1990–2025 is into 2021. Is barley’s sharpest-drop year the same? What does it mean if two different crops share their sharpest drop in the same year?

(d) Explain what it means when two different crops share their sharpest drop in the same year.

(e) Explain why reading the same sharp-drop year across multiple crop charts is stronger evidence of a broad event than a single crop’s chart alone.

Answer

• 1. Line chart, titled/labelled.
• 2. Rising overall; sharpest single-year drop into 2021 (from ~68 bu/ac in 2020 to ~35).
• 3. Yes — both canola and barley have their sharpest drop into 2021.
• 4. It means the poor year hit both crops, pointing to a shared cause rather than a crop-specific one.
• 5. One crop dropping could be crop-specific; multiple crops dropping sharply in the same year points to a broad, shared event.

Question 70

Using the Saskatchewan RM Crop Yields dataset.

(a) Filter to Year = 2019 and build a histogram of the Spring Wheat column, titled with labelled axes.

(b) Describe the shape and estimate the approximate centre by eye.

(c) Compute the mean and median to check your eyeball estimate against the actual centre.

(d) Explain one way a poorly chosen bin width could have led you to misjudge the centre or shape.

(e) State, in one sentence, why a histogram is usually the first chart to make when meeting a new numeric column.

Answer

• 1. Histogram, titled/labelled.
• 2. Roughly symmetric, centred around the high 40s/50 bu/ac.
• 3. mean ≈ 49.23, median ≈ 50.05 bu/ac — close to the eyeball centre.
• 4. Too-wide bins can hide a skew or make a symmetric shape look flat; too-narrow bins add noise that mimics structure.
• 5. A histogram shows the whole distribution’s shape, spread, and centre at a glance, guiding every later choice.

Questions 71–80 · Manitoba Wheat Variety

Question 71

Using the Manitoba Wheat Variety dataset.

(a) Build a histogram of all reported wheat yields, titled with labelled axes.

(b) Describe the shape in Module-1 terms.

(c) The Saskatchewan RM canola yields for a single year are noticeably left-skewed (a long tail of low-yielding RMs). Contrast the shape of this Manitoba wheat histogram with that.

(d) This data has genuine low-end outliers by the 1.5×IQR rule (about 35 of them across all years). Explain how a box plot of the same data would display them.

(e) Explain why a histogram alone might not lead you to call those low values “outliers,” whereas a box plot would flag them explicitly.

Answer

• 1. Histogram, titled/labelled.
• 2. Roughly symmetric and fairly tight (bulk ~54–70; mean 61.2 ≈ median 62.2).
• 3. The SK canola histogram was left-skewed and wider; this one is more symmetric and concentrated.
• 4. A box plot marks values below Q1 − 1.5×IQR (below ≈ 30.4) as individual dots beyond the lower whisker.
• 5. A histogram shows only a thin low tail, leaving “outlier” a judgment call; the box plot applies the 1.5×IQR rule and flags them as points.

Question 72

Using the Manitoba Wheat Variety dataset.

(a) Build a box plot comparing the yield distributions of three widely-grown varieties — AAC BRANDON (BW 932), AAC STARBUCK <SECAN>, and AAC REDBERRY (one box each) — titled with labelled axes.

(b) Explain what a reader learns from this box plot that a simple bar chart of average yield per variety would hide.

(c) Identify, from your chart, which of the three varieties looks the most consistent (least spread), and say how you can tell.

(d) You want to show a general audience that “these varieties yield similarly.” Would you choose the box plot or a bar chart of means? Justify it in terms of audience and message.

(e) Explain one way an unlabelled or poorly titled version of your chart could mislead a reader.

Answer

• 1. Box plot, three named varieties, titled/labelled.
• 2. Spread and consistency (some varieties more variable) plus outliers — a bar of means hides all of that.
• 3. The variety with the narrowest box/shortest whiskers is most consistent (AAC Brandon and AAC Redberry both have IQR ≈ 13, tighter than AAC Starbuck’s ≈ 14). Credit any answer that reads the box widths correctly.
• 4. Defensible either way if justified: a bar-of-means is cleaner for a lay audience whose takeaway is “similar averages”; the box plot is right if the message is consistency.
• 5. A missing y-axis label or a truncated axis could exaggerate small differences between varieties.

Question 73

Using the Manitoba Wheat Variety dataset.

(a) Build a line chart of AAC BRANDON (BW 932)’s average yield by year, 2020–2025, titled with labelled axes.

(b) Identify the year with the lowest average and describe the size of the dip.

(c) Saskatchewan RM canola’s sharpest single-year drop is into 2021. Does this Manitoba variety show its lowest year in the same year? What would that suggest about the cause?

(d) Explain what it means that a single variety, tracked across years, shows the same low year as an entirely different dataset.

(e) Explain why holding the variety fixed and reading across years lets the chart show year effects clearly.

Answer

• 1. Line chart, six points (2020–2025), titled/labelled.
• 2. 2021 is lowest (~49 bu/ac, down from ~62 in 2020).
• 3. Yes — both bottom out in 2021.
• 4. The same low year in two independent datasets points to a shared, region-wide cause that year.
• 5. With the variety fixed, differences between years cannot be due to variety, so the chart isolates the year-to-year effect.

Question 74

Using the Manitoba Wheat Variety dataset.

(a) Filter reported yields to Year = 2023 and build a histogram, titled with labelled axes.

(b) Describe the shape in Module-1 terms.

(c) By the 1.5×IQR rule, 2023 has about 7 low-end outliers. Describe where they would appear on a box plot of this year.

(d) Explain why the count of flagged outliers is smaller for a single year (2023) than it is when all years are pooled together into one dataset.

(e) Explain, in one sentence, how pooling more years can change how many points get flagged as outliers.

Answer

• 1. Histogram, titled/labelled.
• 2. Roughly symmetric, tight (mean 61.4 ≈ median 61.9).
• 3. As individual dots below the lower whisker (below ≈ 33.9 for 2023).
• 4. 2023 alone has fewer rows and a slightly tighter spread than the pooled data, so fewer points fall beyond its fences.
• 5. Pooling years changes the quartiles and IQR, moving the fences and therefore the number of flagged outliers.

Question 75

Using the Manitoba Wheat Variety dataset.

(a) Build a bar chart of the average reported yield for the six most-reported varieties (one bar each), titled with labelled axes.

(b) Identify the tallest and shortest bars.

(c) Explain what this bar chart of average yields shows well, and what it hides compared with a box plot of the same varieties (which would show each variety’s spread and outliers).

(d) Explain why the bars should start at zero, and how a non-zero baseline could mislead a reader about the differences between varieties.

(e) State, in one sentence, when a bar chart of means is the right choice and when a box plot is better.

Answer

• 1. Bar chart, six varieties, titled/labelled.
• 2. Tallest ≈ AAC Wheatland/Hockley (~66–67 median area); shortest ≈ AAC Redberry (~54).
• 3. It shows the averages clearly but hides spread, consistency, and outliers.
• 4. A zero baseline keeps bar heights proportional to the values; a truncated baseline exaggerates small gaps between varieties.
• 5. A bar-of-means suits a simple “compare averages” message; a box plot is better when spread or consistency matters.

Question 76

Using the Manitoba Wheat Variety dataset.

(a) Build a box plot of all reported yields for a single year of your choice with many reports (e.g. 2025), titled with labelled axes.

(b) Read the median, Q1, and Q3 directly off the box.

(c) Compute the same three numbers in Excel and compare them to what you read off the chart.

(d) Explain why reading quartiles off a box plot is quick but approximate, and when you would want the exact numbers instead.

(e) State, in one sentence, what the box (not the whiskers) of a box plot represents.

Answer

• 1. Box plot for 2025, titled/labelled.
• 2. Read median, Q1, Q3 off the box edges and centre line.
• 3. Compute with MEDIAN, QUARTILE.INC(...,1), QUARTILE.INC(...,3) and compare.
• 4. The chart is a visual estimate; use the exact functions when you need precise values (e.g. to report or compute IQR).
• 5. The box spans Q1 to Q3 — the middle 50% of the data, with the median marked inside.

Question 77

Using the Manitoba Wheat Variety dataset.

(a) Build a line chart comparing two varieties’ average yields by year (e.g. AAC BRANDON (BW 932) and AAC STARBUCK <SECAN>), 2020–2025, with a legend, titled with labelled axes.

(b) Describe whether the two varieties track each other across years.

(c) Identify any year where the two lines are closest and any where they are farthest apart.

(d) Explain what it means when two varieties rise and fall together across years (think about what they share: the same years/conditions).

(e) Explain why a legend is essential on a two-line chart.

Answer

• 1. Two-line chart with legend, titled/labelled.
• 2. They largely track each other — both dip in 2021, both recover after.
• 3. Report the closest and farthest years from your chart.
• 4. Sharing the same years means both face the same growing conditions, so common year effects move them together; gaps between them reflect variety differences.
• 5. Without a legend the reader cannot tell which line is which variety.

Question 78

Using the Manitoba Wheat Variety dataset.

(a) Build a histogram of reported yields for the single variety AAC BRANDON (BW 932) (all years), titled with labelled axes.

(b) Describe the shape and estimate the centre by eye.

(c) The histogram of all reported wheat yields (every variety pooled) has an IQR of about 16 bu/ac. Compare this single variety’s spread to that. Which is tighter, and why might that be?

(d) Explain why one variety’s yields might spread less than the pooled mix of all varieties.

(e) State, in one sentence, why comparing a part (one variety) to the whole (all varieties) can reveal how much of the spread comes from mixing varieties.

Answer

• 1. Histogram, titled/labelled.
• 2. Roughly symmetric, centred ~60 bu/ac.
• 3. The single-variety histogram is slightly tighter (IQR ≈ 13 vs the pooled ≈ 15.7).
• 4. One variety removes between-variety differences, leaving mostly site and year variation, so it can spread a little less.
• 5. If the pooled data is much wider than one variety, variety differences add to the spread; if similar, most spread is site/year, not variety.

Question 79

Using the Manitoba Wheat Variety dataset.

(a) Build a box plot comparing reported yields across the years 2020–2025 (one box per year), titled with labelled axes.

(b) Identify the year with the lowest median box.

(c) Describe how the 2021 box compares to the others in position.

(d) Explain what this by-year box plot shows about the drought year that a single line of yearly averages would not.

(e) State, in one sentence, why a box plot per year is a good way to show both the level and the spread changing over time.

Answer

• 1. Box plot, six years, titled/labelled.
• 2. 2021 has the lowest median box.
• 3. The 2021 box sits clearly below the others.
• 4. It shows how the whole distribution dropped in 2021 (lower quartiles, more low outliers), not just the average.
• 5. Each year’s box shows its centre and spread together, so the chart tracks both across time.

Question 80

Using the Manitoba Wheat Variety dataset.

(a) Suppose someone hands you a pie chart showing the “share of reported rows” for the top varieties. Explain why a pie chart is a poor choice for comparing these varieties’ yields.

(b) State what a pie chart can legitimately show for this dataset (think counts/shares, not yields).

(c) Build the more appropriate chart — a bar chart of counts of reported rows for the top six varieties — titled with labelled axes.

(d) Explain why a bar chart lets a reader compare the categories more accurately than a pie chart.

(e) State, in one sentence, the module’s guidance on when a pie chart is acceptable.

Answer

• 1. A pie shows parts of a whole (shares), not a numeric variable like yield; it cannot display yields at all.
• 2. It can show each variety’s share of reported rows (a count-based part-of-whole), though a bar chart does that better.
• 3. Bar chart of reported-row counts for the six varieties, titled/labelled.
• 4. Readers compare bar lengths accurately, but judge pie angles/areas poorly.
• 5. A pie chart is acceptable only to emphasise that one slice is roughly a simple fraction (e.g. about half) of the whole.

Questions 81–90 · Canada Field Crops

Question 81

Using the Canada Field Crops dataset.

(a) Build a box plot comparing canola yield across provinces (one box per province), using all years 2015–2025, titled with labelled axes.

(b) Identify which province sits highest and which shows the widest box.

(c) Explain what “spread” (box width) means for a single province’s box here.

(d) A headline claims “Ontario grows the best canola.” Explain what your box plot supports about that claim.

(e) Explain what the box plot does not show that you would need before agreeing Ontario is the most important canola province (think scale of production, not yield).

Answer

• 1. Box plot, province on the category axis.
• 2. Ontario highest median (~46.5); British Columbia widest box (IQR ≈ 11.0).
• 3. The box width is the range/IQR of that province’s year-to-year yields; wider = more variable across years.
• 4. It supports “higher yield per acre in Ontario.”
• 5. It says nothing about how many acres each province grows; Saskatchewan produces far more overall despite lower per-acre yield.

Question 82

Using the Canada Field Crops dataset.

(a) Build a line chart of national average canola yield by year, 2015–2025, titled with labelled axes.

(b) Identify the lowest year on the chart.

(c) Connect that low year to what you have seen in the Saskatchewan and Manitoba datasets.

(d) Explain why a line chart is the right tool for a “yield over time” story.

(e) Explain why a pie chart would be the wrong tool here.

Answer

• 1. Line chart, year on x, average yield on y, titled/labelled.
• 2. 2021 is the low point (~34 bu/ac, down from ~41 around it).
• 3. The same 2021 drought appears in the SK RM data and the Manitoba varieties — cross-dataset consistency.
• 4. A line chart shows change over an ordered variable (time), making the trend and dip visible.
• 5. A pie chart shows parts of a whole at one moment and cannot show a trend.

Question 83

Using the Canada Field Crops dataset.

(a) Build a bar chart of average canola yield by province for a single year (e.g. 2023), one bar per province, titled with labelled axes.

(b) Identify the tallest and shortest bars.

(c) Explain why the bars should start at zero.

(d) Explain what this single-year bar chart shows well, and what a box plot of the same crop’s yields across many years would add that the bar chart cannot.

(e) State, in one sentence, the difference between what a single-year bar chart and a multi-year box plot tell you about a province.

Answer

• 1. Bar chart, provinces, titled/labelled.
• 2. Report tallest/shortest from the 2023 rows (e.g. Ontario high, BC/SK lower).
• 3. A zero baseline keeps bar heights proportional; a truncated baseline exaggerates province gaps.
• 4. The bar chart shows one year’s averages; the box plot shows each province’s spread across many years.
• 5. The bar chart is a snapshot of one year; the box plot shows the range and consistency over time.

Question 84

Using the Canada Field Crops dataset.

(a) Build a box plot comparing Barley yields across provinces (all years), titled with labelled axes.

(b) Identify the province with the highest median and the one with the widest spread.

(c) Explain how you read a province’s median and IQR off its box.

(d) Explain why comparing provinces with a box plot is fairer than comparing single years, when provinces report different numbers of years.

(e) State, in one sentence, why the box plot’s whiskers and outlier dots matter when comparing provinces.

Answer

• 1. Box plot, provinces, titled/labelled.
• 2. Report the highest-median and widest-box provinces from your chart.
• 3. The median is the line in the box; the IQR is the box height (Q3 − Q1).
• 4. A box plot summarises each province’s whole distribution, so uneven year counts still yield comparable centre/spread pictures.
• 5. Whiskers and outlier dots show each province’s extremes, revealing unusually good or bad years the box alone hides.

Question 85

Using the Canada Field Crops dataset.

(a) Build a line chart with two lines — national average canola yield and national average spring wheat yield by year, 2015–2025 — with a legend, titled with labelled axes.

(b) Describe whether the two crops’ national averages move together across years.

(c) Identify a year where both dip.

(d) Explain why plotting both on the same bu/ac axis is a fair comparison here.

(e) Explain why a legend is required and what would happen to interpretation without it.

Answer

• 1. Two-line chart with legend, titled/labelled.
• 2. They broadly move together across years.
• 3. 2021 — both national averages dip.
• 4. Both are measured in bu/ac, so a shared axis compares like with like.
• 5. The legend maps each line to its crop; without it the two series cannot be told apart.

Question 86

Using the Canada Field Crops dataset.

(a) Build a histogram of all canola yields (all provinces, all years) pooled, titled with labelled axes.

(b) Describe the shape in Module-1 terms.

(c) Explain why pooling provinces and years into one histogram can blur the picture, using the idea that different provinces have different typical yields.

(d) Suggest a better chart than one pooled histogram for showing how canola yield differs by province.

(e) State, in one sentence, when a single pooled histogram is useful and when it hides structure.

Answer

• 1. Histogram, titled/labelled.
• 2. Describe the shape you observe (roughly centred near the low 40s, fairly symmetric).
• 3. Mixing high- and low-yielding provinces stacks several different centres into one histogram, blurring where any single province sits.
• 4. A box plot with one box per province, which separates the provinces instead of pooling them.
• 5. A pooled histogram is useful for the overall spread of all values, but hides differences between the groups it pools.

Question 87

Using the Canada Field Crops dataset.

(a) Build a bar chart of total canola seeded acres by province for 2023 (one bar per province), titled with labelled axes.

(b) Identify the province with the tallest bar.

(c) On a yield-per-acre bar chart, Ontario leads canola. Does the same province lead this seeded-acres chart? Explain what the difference reveals about yield versus total production.

(d) Explain how a province can lead in seeded acres but not in yield per acre, using rate vs. total.

(e) State, in one sentence, why you might show both an acres chart and a yield chart to describe a crop’s provincial picture.

Answer

• 1. Bar chart of seeded acres, provinces, titled/labelled.
• 2. Saskatchewan has by far the tallest bar (~12.4M acres).
• 3. No — Saskatchewan leads seeded acres, but Ontario leads yield per acre.
• 4. Seeded acres is a total (land planted); yield is a rate (per acre) — a province can plant the most land yet not have the highest per-acre yield.
• 5. Acres show scale and yield shows productivity; together they give a complete picture that either alone would distort.

Question 88

Using the Canada Field Crops dataset.

(a) Build a box plot comparing spring wheat yields across provinces (all years), titled with labelled axes.

(b) Identify any province whose box is noticeably higher or lower than the rest.

(c) Explain why a province with only a few reported years will have a box built from few points, and why that box is less reliable.

(d) Explain how you could tell, from the dataset, that a province’s box rests on few observations.

(e) State, in one sentence, why the number of observations behind a box matters when comparing provinces.

Answer

• 1. Box plot, provinces, titled/labelled.
• 2. Report the standout province(s) from your chart.
• 3. Few years means few data points, so its quartiles and whiskers are estimated from little data and can shift with one value.
• 4. Count the non-blank spring wheat rows for that province (e.g. with COUNTIFS).
• 5. A box from many years is more trustworthy than one from a handful; the observation count sets how much to trust the box.

Question 89

Using the Canada Field Crops dataset.

(a) Build a line chart of national average barley yield by year, 2015–2025, titled with labelled axes.

(b) Identify the lowest year.

(c) National average canola yield bottoms out in 2021. Is barley’s lowest year the same? What does two crops sharing their worst national year suggest about the cause?

(d) Explain what it means when two crops share their worst national year.

(e) Explain why viewing several crops’ line charts together is stronger evidence of a broad event than any one chart.

Answer

• 1. Line chart, titled/labelled.
• 2. Report the lowest year (2021 is expected).
• 3. Same year (2021) as canola’s low.
• 4. Both crops falling in the same year points to a shared, broad cause rather than a crop-specific one.
• 5. A single crop’s dip could be crop-specific; several crops dipping together in one year indicates a wide-reaching event.

Question 90

Using the Canada Field Crops dataset.

(a) Choose one province with many reported years (e.g. Saskatchewan) and build a line chart of its canola yield by year, 2015–2025, titled with labelled axes.

(b) Identify the lowest year for that province.

(c) The national average canola yield has its low point in 2021. Compare this province’s low year to that national low year — do they coincide, and what would it mean if a province’s dip were deeper or shallower than the national one?

(d) Explain how a single province’s line can differ from the national line, even though both cover the same years.

(e) State, in one sentence, why showing one province’s line alongside the national line helps a reader see whether that province followed or bucked the national pattern.

Answer

• 1. Line chart for one province, titled/labelled.
• 2. Report that province’s lowest year (2021 expected for SK).
• 3. Compare to the national low (2021); they typically coincide but the province’s dip may be deeper or shallower.
• 4. The national line averages all provinces, so one province can dip more or less than the national average in a given year.
• 5. Overlaying the two lines shows at a glance whether the province tracked the national trend or diverged from it.

---

Section 4 — PivotTables

PivotTables are marked partly on presentation: label your rows/columns clearly and set the right aggregation (Average, Sum, or Count).

Questions 91–100 · Saskatchewan RM Crop Yields (long file)

For this section use the long file rm_yields_1990plus_long.csv, which has the crop name in its own Crop column so you can group by crop.

Question 91

Using the Saskatchewan RM Crop Yields dataset (long file).

(a) Build a PivotTable of average yield by crop for 2023 (Year in Filters = 2023, Crop on Rows, Yield as Average). Rank the crops.

(b) Look at the ranked averages. If one crop’s value looks out of line with the others, identify it and explain why it is so different.

(c) Explain what a reader would wrongly conclude if they took the ranking at face value.

(d) Add Unit to Filters and set it to bu/ac. Identify the highest and lowest crop now.

(e) Explain why the filtered comparison is the fair one.

Answer

• 1. Lentils ≈1314, Oats ≈71, Barley ≈55, Spring Wheat ≈43, Peas ≈34, Canola ≈34, Durum ≈30, Flax ≈20.
• 2. Lentils — reported in lb/ac, everything else in bu/ac.
• 3. That lentils out-yield oats ~18× — a unit artifact, not a real yield difference.
• 4. Filtered to bu/ac: Oats highest (≈71), Flax lowest (≈20).
• 5. All crops then share one unit, so the magnitudes are comparable.

Question 92

Using the Saskatchewan RM Crop Yields dataset (long file).

(a) Build a PivotTable with Year on Rows and Crop on Columns (Yield as Average, Unit filtered to bu/ac).

(b) Compare canola in 2021 versus 2023, and describe the size of the change in plain language.

(c) Scan the 2021 row across all crops. Describe what you notice.

(d) Explain how a whole low row (a year) differs, in cause, from a single low column value (one crop in one year).

(e) State what the grid lets you conclude about 2021, and why.

Answer

• 1. Year × Crop grid of average yields.
• 2. Canola in 2021 (≈21.9) is about one-third lower than 2023 (≈33.9) — a large drop.
• 3. Every crop’s 2021 average is low, not just canola.
• 4. A low row = a broad cause hitting all crops that year; a single low column value = a crop-specific problem that year.
• 5. 2021 was a broad, region-wide low year — the whole row is depressed, so the cause was general rather than crop-specific.

Question 93

Using the Saskatchewan RM Crop Yields dataset (long file).

(a) Build a single PivotTable with Crop on Rows and Year on Columns (Yield as Average, Unit = bu/ac), then filter the columns to just 2019 and 2025.

(b) For each crop, read the 2019 and 2025 values and compute the change (2025 minus 2019).

(c) State which crop rose the most in absolute bu/ac between those two years.

(d) Explain why the crop that rose most in absolute terms is not necessarily the one that rose most in relative terms.

(e) Describe how you would use the PivotTable output to compute a relative change for each crop.

Answer

• 1. One Crop × Year pivot, columns filtered to 2019 and 2025.
• 2. 2025 minus 2019 per crop (e.g. Oats 97.5 − 89.4 ≈ 8.1; Barley 72.8 − 68.6 ≈ 4.2; Canola 43.9 − 40.8 ≈ 3.1).
• 3. Oats rose most in absolute bu/ac (~8).
• 4. Oats already yield the most, so a big absolute gain can be a small percentage; a lower-yielding crop’s smaller gain could be a larger percentage.
• 5. Divide each crop’s change by its 2019 value to get the relative (percent) change.

Question 94

Using the Saskatchewan RM Crop Yields dataset (long file).

(a) Build a PivotTable of the count of reporting RMs for canola each year (Crop filtered to Canola, Year on Rows, Yield as Count).

(b) State roughly how many RMs report canola in a typical recent year.

(c) Explain what a change in the yearly count would tell you about reporting coverage, using counts only.

(d) Explain why you would use Count (not Average) for this particular question.

(e) State, in one sentence, the difference between a PivotTable Count and a PivotTable Average of the same field.

Answer

• 1. Year on Rows, Yield → Values set to Count.
• 2. About 290 RMs report canola in recent years.
• 3. A stable count means steady coverage; a dip would signal fewer RMs reporting that year.
• 4. Count answers “how many RMs reported,” whereas Average answers “what did they yield” — here you want the number of reports.
• 5. Count tallies how many values there are; Average gives their mean — one measures coverage, the other level.

Question 95

Using the Saskatchewan RM Crop Yields dataset (long file).

(a) Build a PivotTable with Crop on Rows and Yield as both Average and Count (two value columns), filtered to bu/ac, for 2023.

(b) Identify which crop has the most reports and which has the fewest.

(c) Explain why a crop with fewer reports has a less reliable average, using the count column.

(d) Explain how having Count next to Average in the same PivotTable helps you judge each average.

(e) State, in one sentence, why you would show both Average and Count when presenting a by-crop summary.

Answer

• 1. Crop on Rows; Yield → Average and Yield → Count.
• 2. Report the most- and fewest-reported crops from the count column (e.g. Spring Wheat/Barley high, Flax/Durum lower in 2023).
• 3. A crop with fewer reporting RMs has an average built on less data, so it is more easily swayed by a few RMs.
• 4. The count tells you how much data supports each average, so you know which averages to trust more.
• 5. The average states the level and the count states its support — together they let a reader weigh each figure.

Question 96

Using the Saskatchewan RM Crop Yields dataset (long file).

(a) Build a PivotTable with Crop on Rows and Year on Columns (Yield as Average, bu/ac), covering 2019–2023.

(b) Read across the Canola row. Identify its lowest year in that span.

(c) Read across the Barley row. Does it share the same lowest year?

(d) Explain what it means when two different crop rows share their lowest year in the same grid.

(e) State, in one sentence, why a Crop × Year grid is an efficient way to spot a year that hit many crops at once.

Answer

• 1. Crop × Year grid.
• 2. Canola’s lowest in 2019–2023 is 2021 (≈21.9).
• 3. Yes — barley’s lowest in that span is also 2021.
• 4. Both crops bottoming in the same year points to a shared, broad cause that year.
• 5. The grid lets you scan a single column (year) across all crop rows at once, so a broadly bad year stands out immediately.

Question 97

Using the Saskatchewan RM Crop Yields dataset (long file).

(a) Build a PivotTable of average yield by crop across all years (Crop on Rows, Yield as Average, bu/ac).

(b) Rank the crops by their long-run average.

(c) In the 2023-only ranking, Oats is the highest-yielding crop and Flax the lowest (among bu/ac crops). Compare that to your all-years ranking — is the order the same, and why might a single-year ranking differ from the long-run one?

(d) Explain why a long-run average ranking can differ from a single-year ranking.

(e) State, in one sentence, when you would prefer an all-years average over a single-year average.

Answer

• 1. Crop on Rows, Yield Average, all years.
• 2. Oats highest, then Barley, Spring Wheat, Durum/Peas/Canola, Flax lowest (bu/ac).
• 3. Largely the same order (Oats > Barley > Spring Wheat > … > Flax), with minor shuffles among the middle crops.
• 4. One year’s weather can lift or drop a crop out of its usual position, so a single year need not match the long-run order.
• 5. Use the all-years average when you want a stable, typical ranking rather than one year’s result.

Question 98

Using the Saskatchewan RM Crop Yields dataset (long file).

(a) Build a PivotTable with Year on Rows and Yield as Average for Oats only (Crop filtered to Oats), 2019–2025.

(b) Identify the highest and lowest years for oats in that span.

(c) Explain how filtering to one crop before pivoting by year isolates that crop’s year-to-year pattern.

(d) Explain why you must set the aggregation to Average (not Sum) for a yield question like this.

(e) State, in one sentence, why summing yields across RMs would produce a meaningless number.

Answer

• 1. Year on Rows, Yield Average, Crop = Oats.
• 2. Highest ≈ 2025 (~97), lowest ≈ 2021 (~45).
• 3. With only oats in the pivot, the year rows show oats’ own averages, uncontaminated by other crops.
• 4. Average gives the mean yield per RM; Sum would add per-acre rates across RMs, which is not a meaningful total.
• 5. Yield is a per-acre rate, so adding rates across RMs gives a number with no physical meaning.

Question 99

Using the Saskatchewan RM Crop Yields dataset (long file).

(a) Build a PivotTable with Crop on Rows and Yield as Average (bu/ac) filtered to 2021 (the low year).

(b) Rank the crops for 2021.

(c) In 2023, Oats is the top-ranked crop by yield. Compare your 2021 (drought-year) ranking to that: does the drought change which crop is on top, or does it scale all crops down together while keeping a similar order?

(d) Explain what it tells you if the ranking of crops stays similar even when every crop’s yield falls.

(e) State, in one sentence, the difference between the level of yields changing and the ranking of crops changing.

Answer

• 1. Crop on Rows, Yield Average, 2021.
• 2. Oats > Barley > Spring Wheat > Peas > Canola > Durum > Flax (all lower than 2023).
• 3. The top crop (Oats) is unchanged; the drought lowered every crop but kept a similar order.
• 4. It means the drought scaled all crops down together without changing their relative standing.
• 5. The level is how high the yields are; the ranking is their order — a bad year can drop the level while leaving the order intact.

Question 100

Using the Saskatchewan RM Crop Yields dataset (long file).

(a) Build a PivotTable with Year on Rows and Yield as Average for Canola only, and use it to create a PivotChart (line).

(b) Describe the overall direction of the line and identify the year with the sharpest single-year drop.

(c) Explain the advantage of a PivotChart over a static chart when the underlying PivotTable filters change.

(d) Explain why building the by-year average in a PivotTable first is easier than computing 36 yearly averages by hand.

(e) State, in one sentence, how PivotTables and charts work together to summarise a large dataset.

Answer

• 1. PivotChart line of canola average by year.
• 2. Rising overall; the sharpest single-year drop is into 2021 (from ~38 to ~22 bu/ac). (The lowest level is 2002 at ~16; here the question asks for the sharpest drop.)
• 3. A PivotChart updates automatically when you change the PivotTable’s filters or fields.
• 4. The PivotTable computes every year’s average in one step, instead of writing an AVERAGEIF per year.
• 5. The PivotTable aggregates the raw rows into a summary, and the chart renders that summary visually.

Questions 101–110 · Manitoba Wheat Variety

Question 101

Using the Manitoba Wheat Variety dataset.

(a) Build a PivotTable of average reported yield by variety (Variety on Rows, Yield_bu_ac as Average), and add a Count of the rows behind each average.

(b) Identify the variety with the highest average, and the variety that is most widely reported (highest count).

(c) Explain why “highest average” and “most widely reported” can be two different varieties.

(d) Say which of the two you would trust more as a summary, and justify it with the count.

(e) Explain why filtering to varieties with at least 30 reports gives a more trustworthy ranking than including every variety.

Answer

• 1. Variety on Rows; Yield Average + Count.
• 2. Highest average (unfiltered) ≈ AAC WESTKING (~79) — but on only ~17 reports; most-reported ≈ AAC BRANDON (BW 932) (~519 reports, mean ~60). (Among well-reported varieties with ≥30 reports, SY MANNESS ~72 is the top.)
• 3. A variety reported on very few sites can post a high average without being widely reported.
• 4. The widely-reported average (AAC Brandon, n≈519) is more trustworthy than a high average on ~17 reports.
• 5. Averages from a handful of reports are noisy and can top the list by chance (AAC WESTKING on ~17); a ≥30 threshold keeps only well-supported estimates.

Question 102

Using the Manitoba Wheat Variety dataset.

(a) Build a PivotTable with Variety on Rows and Year on Columns (Yield as Average) for a few common varieties including AAC BRANDON (BW 932).

(b) Identify AAC Brandon’s weakest year, and describe the size of the dip.

(c) Read across the other varieties’ rows for that same year. Do they dip too?

(d) Explain what it means that several varieties share their weakest year.

(e) Explain how reading the same variety across years isolates the year effect from the variety.

Answer

• 1. Variety × Year grid.
• 2. AAC Brandon’s 2021 (~49) is well below its ~62–66 in other years.
• 3. Yes — other varieties (e.g. Starbuck ~51 in 2021) dip in the same year.
• 4. A shared weak year across varieties points to a common year effect rather than a variety fault.
• 5. With the variety fixed, year-to-year changes cannot be due to variety, so they reflect the year’s conditions.

Question 103

Using the Manitoba Wheat Variety dataset.

(a) Build a PivotTable of the count of reported rows by variety (Variety on Rows, Yield as Count).

(b) Identify the three most-reported varieties.

(c) Explain what this count-based PivotTable tells you that an average-yield PivotTable does not.

(d) Explain why you would filter out varieties with very low counts before ranking varieties by average yield.

(e) State, in one sentence, why a count PivotTable is a good first step before comparing variety averages.

Answer

• 1. Variety on Rows, Yield → Count.
• 2. AAC Brandon (~519), AAC Starbuck (~374), AAC Wheatland (~219).
• 3. It shows how much data backs each variety — coverage — not how well each yielded.
• 4. Low-count varieties have unstable averages that can distort a ranking.
• 5. Knowing the counts first tells you which variety averages are well-supported enough to compare.

Question 104

Using the Manitoba Wheat Variety dataset.

(a) Build a PivotTable with Year on Rows and Yield as Average (all reported rows), 2020–2025.

(b) Identify the lowest and highest years.

(c) Describe the overall movement of the yearly averages across the six years.

(d) Explain why the yearly average can be affected by which varieties were reported that year, and how a Count column would help you check.

(e) State, in one sentence, why adding a Count column to a by-year PivotTable improves your interpretation.

Answer

• 1. Year on Rows, Yield Average.
• 2. Lowest 2021 (~49.6), highest 2025 (~68.0).
• 3. A dip in 2021 then a steady climb to 2025.
• 4. If the mix of reported varieties changes yearly, the average shifts partly for that reason; a Count column shows how many rows (and which years) are thin.
• 5. The Count shows how much data supports each year’s average, flagging years to interpret cautiously.

Question 105

Using the Manitoba Wheat Variety dataset.

(a) Build a PivotTable with Municipality on Rows and Yield as Average (all reported rows) for one year (e.g. 2023).

(b) Identify the highest- and lowest-average municipalities that year.

(c) Explain why a municipality with only one or two reported varieties will have a shaky average, and how a Count column reveals that.

(d) Explain why comparing municipalities is fairer when each rests on several reported varieties.

(e) State, in one sentence, why you would add a Count column before ranking municipalities.

Answer

• 1. Municipality on Rows, Yield Average, Year = 2023.
• 2. Report the top and bottom municipalities from your pivot.
• 3. One or two varieties give an average from very little data; a Count column exposes those thin municipalities.
• 4. With several varieties each, the municipal averages rest on comparable amounts of data.
• 5. The Count flags municipalities whose averages come from too few reports to rank reliably.

Question 106

Using the Manitoba Wheat Variety dataset.

(a) Build a PivotTable with Variety on Rows and Yield as Average, filtered to a single year (e.g. 2025), and add Count.

(b) Rank the varieties for that year (among those with several reports).

(c) Across all years, SY MANNESS is the top-averaging variety (among those with many reports). Compare your single-year ranking to that all-years picture — does the same variety lead, and why can a variety’s rank shift from one year to the next?

(d) Explain why a variety’s rank can change from one year to the next.

(e) State, in one sentence, why an all-years variety ranking is generally more stable than a one-year ranking.

Answer

• 1. Variety on Rows, Yield Average + Count, Year = 2025.
• 2. Rank the well-reported varieties for 2025.
• 3. Compare to the all-years order; expect broad agreement with some movement.
• 4. A single year’s conditions can favour some varieties over others, shifting the order.
• 5. Averaging over many years smooths out any one year’s quirks, giving a steadier ranking.

Question 107

Using the Manitoba Wheat Variety dataset.

(a) Build a PivotTable with Variety on Rows and both Average and Count of Yield, for the whole dataset.

(b) Filter (or sort) to show only varieties with at least 30 reports.

(c) Among those, identify the highest and lowest average.

(d) Explain why applying the ≥30 filter changes which variety appears “best” compared with an unfiltered ranking.

(e) State, in one sentence, why a minimum-count filter is a form of quality control on a ranking.

Answer

• 1. Variety on Rows, Yield Average + Count.
• 2. Keep varieties with Count ≥ 30.
• 3. Highest ≈ SY MANNESS (~72); lowest among the well-reported ≈ AAC Tisdale/Redberry (~54).
• 4. Without the filter, a variety with 1–2 lucky reports could top the list; the filter removes those unreliable entries.
• 5. It ensures every ranked average rests on enough data to be trusted.

Question 108

Using the Manitoba Wheat Variety dataset.

(a) Build a PivotTable with Year on Rows and Variety on Columns (Yield as Average) for two varieties (e.g. AAC Brandon and AAC Starbuck).

(b) For each year, note which variety yielded higher.

(c) State whether one variety beats the other in every year, or whether the lead changes.

(d) Explain how this grid separates a consistent variety advantage from a year-specific one.

(e) State, in one sentence, why comparing two varieties across many years is more convincing than comparing them in a single year.

Answer

• 1. Year × Variety grid for the two varieties.
• 2. Compare the two columns each year (Starbuck is generally higher).
• 3. Starbuck leads in most/all years here — a consistent edge, not a one-year fluke.
• 4. A lead that holds every year is a genuine variety difference; a lead in only one year could be that year’s conditions.
• 5. A consistent multi-year lead rules out chance and single-year effects far better than one comparison.

Question 109

Using the Manitoba Wheat Variety dataset.

(a) Build a PivotTable of average yield by variety, filter to varieties with at least 30 reports, and turn the highest-averaging ones into a bar PivotChart (ranked by average yield), titled with labelled axes.

(b) Identify the tallest and shortest bars.

(c) Explain what the bar chart shows well and what it hides (compared to also seeing each variety’s Count and spread).

(d) Explain why the bars should start at zero.

(e) State, in one sentence, how a PivotChart keeps the chart in sync with the PivotTable.

Answer

• 1. Bar PivotChart of variety averages (≥30 reports), ranked, titled/labelled.
• 2. Tallest ≈ SY MANNESS (~72, the highest average among well-reported varieties); shortest among these ≈ AAC Tisdale/Redberry (~54).
• 3. It shows the averages clearly but hides how many reports back each and how spread each variety is.
• 4. A zero baseline keeps bar heights proportional; a truncated axis exaggerates small gaps.
• 5. A PivotChart redraws automatically whenever the PivotTable’s fields or filters change.

Question 110

Using the Manitoba Wheat Variety dataset.

(a) Build a PivotTable that counts reported vs suppressed rows by variety (Variety on Rows, Reported on Columns, Yield as Count).

(b) Pick one variety and read off how many of its rows are reported vs suppressed.

(c) Explain what a high suppressed count for a variety tells you about how its data appears in this dataset.

(d) Explain why this reported-vs-suppressed PivotTable is impossible to build if you first delete all suppressed rows.

(e) State, in one sentence, why keeping the suppressed rows (with a flag) makes the dataset more informative.

Answer

• 1. Variety on Rows, Reported on Columns, Count of Yield.
• 2. Report the TRUE/FALSE split for your chosen variety (e.g. AAC Brandon ≈ 519 TRUE, ≈ 51 FALSE).
• 3. A high suppressed count means the variety appears in many places but with yields often withheld.
• 4. Deleting suppressed rows removes the FALSE column entirely, so the split cannot be shown.
• 5. The flagged suppressed rows let you measure coverage and withholding, not just reported yields.

Questions 111–120 · Canada Field Crops

Question 111

Using the Canada Field Crops dataset.

(a) Build a PivotTable of average canola yield by province for 2023 (Province on Rows, Yield as Average, Year filtered to 2023). Rank the provinces.

(b) Identify the top-ranked province and Saskatchewan’s rank.

(c) Explain what this pivot does and does not establish about which province is the more important canola province.

(d) Add Seeded_acres as a second value (Sum). Describe how looking at yield and acreage together changes the story.

(e) State the general lesson about distinguishing “yield per acre” from “total production.”

Answer

• 1. Ranking by average yield, 2023: Ontario 53.4, New Brunswick 45.6, Manitoba 44.6, Quebec 40.9, Alberta 40.1, Saskatchewan 37.4, BC 35.1.
• 2. Ontario ranks top; Saskatchewan is near the bottom on yield per acre.
• 3. The pivot ranks yield per acre; it does not establish importance/output.
• 4. Saskatchewan seeds ~12.4M acres vs Ontario’s ~45,100 — SK produces vastly more canola overall despite lower yield.
• 5. High yield per acre ≠ high total production; you need acreage to judge importance.

Question 112

Using the Canada Field Crops dataset.

(a) Build a PivotTable with Province on Rows and Year on Columns (canola Yield as Average).

(b) Reading across Saskatchewan’s row, identify its weakest year and describe the gap to a normal year.

(c) Reading down the 2021 column, describe what you notice across provinces.

(d) Explain how this grid lets you tell whether 2021 was a local Saskatchewan problem or a broader one.

(e) State your conclusion about 2021 and the evidence in the grid that supports it.

Answer

• 1. Province × Year grid of average canola yield.
• 2. Saskatchewan’s 2021 is well below its other years.
• 3. Several provinces are low in 2021, not just Saskatchewan.
• 4. If the whole 2021 column is low, the cause is broad; if only SK’s cell is low, it’s local.
• 5. 2021 was a broad, multi-province low year — several provinces dip together in that column.

Question 113

Using the Canada Field Crops dataset.

(a) Build a PivotTable of total canola seeded acres by province for 2023 (Province on Rows, Seeded_acres as Sum, Year = 2023). Rank the provinces.

(b) Identify the province with the most seeded acres and its rough share of the national total.

(c) By yield per acre, Ontario leads canola. Does the same province lead this seeded-acres ranking? Explain how a province can lead one but not the other.

(d) Explain how a province can lead seeded acres but rank low on yield per acre.

(e) State, in one sentence, why “which province grows the most canola” is answered by acres (or production), not by yield.

Answer

• 1. Seeded acres 2023: SK ≈12.4M, AB ≈6.36M, MB ≈3.13M, then far smaller (BC ~109k, ON ~45k, QC ~39k, NB ~1.6k).
• 2. Saskatchewan — over half the national canola acreage.
• 3. No — SK leads acres but ranks low on yield per acre (Ontario led yield).
• 4. Yield is per-acre performance while acres is land planted; a province can plant the most land yet not yield the most per acre.
• 5. “Grows the most” is about scale (acres/production), which yield per acre does not measure.

Question 114

Using the Canada Field Crops dataset.

(a) Build a PivotTable with Crop on Rows and average Yield across all provinces and years (Yield as Average).

(b) Rank the crops nationally.

(c) Identify the top crop and explain why its high average does not, by itself, tell you it is the most-grown crop.

(d) Explain what additional field you would pivot on (Sum) to judge how much of each crop is grown.

(e) State, in one sentence, why a by-crop yield pivot and a by-crop acres pivot answer different questions.

Answer

• 1. Crop on Rows, Yield Average.
• 2. Corn for grain highest (~136), then Oats (~79), Barley (~64) … down to Flax (~26).
• 3. Corn yields the most per acre, but a high per-acre yield says nothing about how many acres of corn are grown.
• 4. Pivot Seeded_acres as Sum by crop to see how much of each is planted.
• 5. The yield pivot ranks per-acre productivity; the acres pivot ranks scale — different questions.

Question 115

Using the Canada Field Crops dataset.

(a) Build a PivotTable of average canola yield by province across all years (Province on Rows, Yield as Average).

(b) Rank the provinces by average canola yield. In the 2023-only ranking, Ontario is highest and Saskatchewan sits near the bottom on yield per acre — is your all-years order similar, and why might a single year differ from the long-run ranking?

(c) State whether the top province is the same in both.

(d) Explain why an all-years ranking can differ from a single-year ranking.

(e) State, in one sentence, when you would use the all-years provincial ranking rather than one year’s.

Answer

• 1. All-years averages: Ontario 46.7, NB 44.0, MB 41.5, QC 41.4, PEI 40.7, AB 40.1, SK 39.1, BC 35.5.
• 2. Similar order to 2023, with small shuffles in the middle.
• 3. Yes — Ontario tops both.
• 4. One year’s conditions can move a province up or down, so a single year need not match the long-run order.
• 5. Use the all-years ranking for a stable, typical comparison rather than one year’s snapshot.

Question 116

Using the Canada Field Crops dataset.

(a) Build a PivotTable with Year on Rows and Sum of canola Seeded_acres (all provinces), 2015–2025.

(b) Describe the overall trend in national canola seeded acres over the period.

(c) Explain why summing seeded acres across provinces is a valid total, unlike summing yields.

(d) Explain why you set the aggregation to Sum here, not Average.

(e) State, in one sentence, what this by-year acres total tells you that a by-year yield average would not.

Answer

• 1. Year on Rows, Seeded_acres as Sum.
• 2. Describe the trend you see in total canola acres over 2015–2025.
• 3. Acres add up (extensive), so the national total is meaningful; yields are per-acre rates and cannot be summed.
• 4. Sum gives the national total acreage; Average would give a per-province average acreage, a different quantity.
• 5. It shows the crop’s changing national footprint (how much land), not how well it yielded.

Question 117

Using the Canada Field Crops dataset.

(a) Build a PivotTable with Province on Rows and Sum of Seeded_acres for spring wheat in 2023.

(b) Rank the provinces by seeded acres.

(c) Add average Yield as a second value. Identify a province that ranks high on acres but not on yield (or vice versa).

(d) Explain how showing acres and yield side by side tells a fuller story than either alone.

(e) State, in one sentence, why a province’s contribution to national production depends on both its acres and its yield.

Answer

• 1. Province on Rows, Seeded_acres as Sum, spring wheat 2023.
• 2. Prairie provinces (SK, then AB, MB) lead seeded acres.
• 3. Report a province high on one measure but not the other from your pivot.
• 4. Acres show scale, yield shows per-acre performance; together they explain which provinces drive production.
• 5. Production = yield × acres, so a province needs both to contribute heavily to the national total.

Question 118

Using the Canada Field Crops dataset.

(a) Build a PivotTable with Crop on Rows and Count of non-blank Yield values (Yield as Count), all provinces and years.

(b) Identify a crop with a high count and one with a low count.

(c) Explain what a low count tells you about how many province-years reported that crop.

(d) Explain why a crop’s low count means its national average rests on fewer province-years.

(e) State, in one sentence, why a Count PivotTable is a useful check before trusting a crop’s national average.

Answer

• 1. Crop on Rows, Yield → Count.
• 2. Widely grown crops (canola, barley, spring wheat) have high counts; durum and flax have lower counts.
• 3. A low count means few province-years reported that crop — it is grown in fewer places.
• 4. Fewer reporting province-years means the national average is built from less data and covers less of the country.
• 5. The count shows how broadly a crop is reported, so you know how far its national average really extends.

Question 119

Using the Canada Field Crops dataset.

(a) Build a PivotTable with Province on Rows and Year on Columns (Sum of canola Seeded_acres).

(b) Reading across Saskatchewan’s row, describe how its canola acreage changed over the years.

(c) Reading down a recent year’s column, confirm which province plants the most canola.

(d) Explain why a Province × Year acres grid is a good way to see both who grows the most and how their acreage is changing.

(e) State, in one sentence, why Sum (not Average) is the correct aggregation for a seeded-acres grid.

Answer

• 1. Province × Year grid, Seeded_acres as Sum.
• 2. Describe SK’s canola acreage path across the years from your grid.
• 3. Saskatchewan plants the most canola in recent years (~12M+ acres).
• 4. Rows show each province’s trend; columns show who leads in a given year — the grid shows both at once.
• 5. Acres are additive totals, so Sum gives each province-year’s true acreage; Average would misrepresent it.

Question 120

Using the Canada Field Crops dataset.

(a) Build a PivotTable of average canola yield by province (all years) and turn it into a bar PivotChart, titled with labelled axes.

(b) Identify the highest and lowest province bars.

(c) Explain why this yield chart alone could mislead someone into thinking the top-bar province is Canada’s biggest canola producer.

(d) Describe the second chart (a bar chart of Sum of seeded acres by province) you would place beside it to complete the story.

(e) State, in one sentence, the overall lesson this pair of charts teaches about yield versus production.

Answer

• 1. Bar PivotChart of provincial canola yield averages, titled/labelled.
• 2. Highest ≈ Ontario; lowest ≈ British Columbia.
• 3. A viewer might assume the highest-yield province grows the most canola, but yield per acre is not production.
• 4. A bar chart of Sum of seeded acres by province, which would show Saskatchewan towering over the rest.
• 5. The province that yields the most per acre is not the one that produces the most canola — you need both yield and acres.