Module 1 — Test Bank

Instructions

Each practice test consists of four questions, one from each type below. Download the dataset, complete the work in Excel, and check your answers by expanding the answer section below each question.

On the real test, you will receive a PDF with four questions and will submit a .xlsx workbook with your answers. You will have access to the full test bank for practice — the test draws one question randomly from each type.

Datasets

This module provides the same Saskatchewan crop-yield data in two shapes. Use whichever fits the question — see Wide vs. Long Data in the module for why the shape matters.

Wide format — rm_yields_1990plus.csv — one row per RM-year, each crop in its own column. Use this for descriptive statistics, conditional functions, and lookups (Types 1, 2, and the chart questions).

Column Excel col Description
Year A Crop year (1990–2025)
RM B Rural Municipality number (1–622)
Spring Wheat C Spring wheat yield (bu/ac)
Durum D Durum yield (bu/ac)
Canola E Canola yield (bu/ac)
Barley F Barley yield (bu/ac)
Oats G Oats yield (bu/ac)
Peas H Field pea yield (bu/ac)
Lentils I Lentil yield (lb/ac)
Flax J Flax yield (bu/ac)

Long format — rm_yields_1990plus_long.csv — one row per RM-year-crop, with the crop name in its own column. Use this for PivotTables (Type 3), where you want to group by crop.

Column Excel col Description
Year A Crop year (1990–2025)
RM B Rural Municipality number
Crop C Crop name (one of the eight crops)
Yield D Yield value for that crop
Unit E bu/ac for all crops except Lentils (lb/ac)

Both files come from the Government of Saskatchewan’s RM Yields dataset, filtered to 1990 onward and the eight most widely grown crops. They contain identical information — the long file simply stacks the crop columns of the wide file.

Important — this is real data, so it has gaps. In the wide file a cell is blank when that crop was not grown (or not reported) in that RM and year; Excel’s AVERAGE, COUNT, MEDIAN, etc. automatically skip blank cells. In the long file, those non-observations are simply absent (no row), which is why the long file has fewer rows than 8 × the wide file. Either way: never fill a blank with 0 (a blank means “no data,” not “a yield of zero”).

Import each CSV using Data → Get Data → From Text/CSV (not by double-clicking) to avoid Excel mangling any values.

A note on the numbers. Because Saskatchewan updates this dataset over time, the exact figures in the answer keys are stated as of the current data snapshot. The important thing is the method — the right filter and the right formula — which stays correct even if a number shifts slightly when the data is refreshed.

Type 1: Descriptive Statistics

Several of these questions focus on a single crop in a single year. The cleanest approach in Excel is to first filter the data to that year (Data → Filter, or copy the year’s rows to a new sheet), then compute the statistics on that crop’s column. Place each answer in a clearly labelled cell, using an Excel formula (not a hard-coded value).

Variant 1A — Canola in 2023

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

(a) Compute the mean canola yield across all RMs that reported canola in 2023.

(b) Compute the median canola yield.

(c) Compute the sample standard deviation of canola yield.

(d) Compute the first quartile (Q1) and third quartile (Q3), and the interquartile range (IQR).

(e) Compute the 90th percentile of canola yield. Write one sentence explaining what it means in plain language.

(f) Is the mean or the median higher? What does this tell you about the shape of the distribution?

Answers

Values as of the current snapshot; 2023 has 295 RM rows, of which 289 reported canola (6 blanks).

Part Value Formula (assuming 2023 Canola values are in E2:E296)
(a) Mean ≈ 33.88 bu/ac =AVERAGE(E2:E296)
(b) Median ≈ 35.50 bu/ac =MEDIAN(E2:E296)
(c) Std dev ≈ 13.33 bu/ac =STDEV.S(E2:E296)
(d) Q1 ≈ 22.60 bu/ac =QUARTILE.INC(E2:E296, 1)
(d) Q3 ≈ 44.90 bu/ac =QUARTILE.INC(E2:E296, 3)
(d) IQR ≈ 22.30 bu/ac =QUARTILE.INC(E2:E296,3) - QUARTILE.INC(E2:E296,1)
(e) P90 ≈ 50.44 bu/ac =PERCENTILE.INC(E2:E296, 0.9)

Part (e) interpretation: About 90% of reporting RMs had a 2023 canola yield of ~50.4 bu/ac or less; only the top 10% of RMs exceeded it.

Part (f): The median (≈35.5) is higher than the mean (≈33.9), indicating a slight left (negative) skew — a number of low-yielding RMs (some as low as ~3 bu/ac, likely drought-hit) pull the mean below the median.

Common mistakes:

  • Filtering first is essential. If you compute AVERAGE on the whole (unfiltered) Canola column you get the 1990–2025 average (≈28.3), not the 2023 average.
  • Using STDEV.P instead of STDEV.S. Use .S for a sample.
  • Filling blank cells with 0 — this drags the mean down and is wrong. Leave blanks blank; AVERAGE skips them.
  • Forgetting PERCENTILE.INC takes a decimal (0.9), not 90.

Variant 1B — Spring Wheat in 2023

Filter the data to Year = 2023 and work with the Spring Wheat column.

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

(b) Compute the sample standard deviation and sample variance. What are the units of the variance?

(c) Compute the minimum, maximum, and range of spring wheat yield.

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

(e) How many RMs reported a spring wheat yield in 2023? (Count the non-blank cells.)

Answers

Values as of the current snapshot; assume the 2023 Spring Wheat values are in column C.

Part Value Formula
(a) Mean ≈ 42.87 bu/ac =AVERAGE(C2:C296)
(a) Median ≈ 45.80 bu/ac =MEDIAN(C2:C296)
(b) Std dev ≈ 17.22 bu/ac =STDEV.S(C2:C296)
(b) Variance ≈ 296.5 (bu/ac)² =VAR.S(C2:C296)
(c) Min / Max (lowest / highest reported) =MIN(C2:C296), =MAX(C2:C296)
(c) Range Max − Min =MAX(C2:C296) - MIN(C2:C296)
(e) Count ≈ 285 RMs =COUNT(C2:C296)

Part (b): Variance units are squared — (bu/ac)² — because variance averages the squared deviations. This is why standard deviation (the square root) is usually reported instead: it is back in bu/ac.

Part (e): Use COUNT (counts numbers, skips blanks), not COUNTA (which would count text) and not the total row count. A handful of RMs did not report spring wheat, so the count is below the ~295 total rows.

Common mistakes:

  • Reporting the total number of 2023 rows (295) instead of the number that actually reported spring wheat.
  • Confusing standard deviation with variance — they differ by a square.

Type 2: Conditional Functions and Lookups

These questions use conditional functions (COUNTIF, AVERAGEIF, SUMIF, COUNTIFS) and lookups (XLOOKUP, INDEX/MATCH) across the full, unfiltered dataset. Each answer should use an Excel formula.

Throughout, assume the data occupies the range shown by your import (row 1 is the header; columns A–J as in the table above). Adjust the row numbers to match your sheet.

Variant 2A

(a) Across all years and RMs, how many rows have a canola yield strictly greater than 40 bu/ac?

(b) What is the average canola yield in the year 2023 only? (Use AVERAGEIF on the Year column — no manual filtering.)

(c) What is the average canola yield in 2010 versus 2021? (2021 was a severe drought year.) Which is higher, and by how much?

(d) Use a lookup to find the canola yield in RM 1 in 2023. (Hint: you will need to match on two conditions — RM and Year. XLOOKUP with a concatenated key, or INDEX/MATCH with two criteria, both work.)

(e) Write a formula that classifies each row’s canola yield as "Low" (below 20), "Medium" (20 to 40), or "High" (above 40).

Answers

Assume Year = col A, RM = col B, Canola = col E, data in rows 2:10650.

Part Value Formula
(a) Count > 40 (snapshot value) =COUNTIF(E2:E10650, ">40")
(b) Avg 2023 ≈ 33.88 bu/ac =AVERAGEIF(A2:A10650, 2023, E2:E10650)
(c) Avg 2010 / 2021 2010 much higher =AVERAGEIF(A2:A10650, 2010, E2:E10650) and =AVERAGEIF(A2:A10650, 2021, E2:E10650)
(d) RM 1, 2023 canola ≈ 36.8 bu/ac see below

Part (d) — two-criteria lookup. The tidiest modern approach is a XLOOKUP on a concatenated key, or a boolean XLOOKUP:

=XLOOKUP(1&"|"&2023, B2:B10650&"|"&A2:A10650, E2:E10650)

or with INDEX/MATCH:

=INDEX(E2:E10650, MATCH(1&"|"&2023, B2:B10650&"|"&A2:A10650, 0))

(Enter as an array formula in older Excel; modern Excel handles it directly.)

Part (e):

=IFS(E2<20, "Low", E2<=40, "Medium", TRUE, "High")

Copy down all rows. Blank cells will need handling — wrap in an IF to leave blanks blank: =IF(E2="", "", IFS(...)).

Notes:

  • For (c), the drought contrast is real and stark — 2021 canola yields were far below 2010. This is the kind of pattern the data is good for.
  • AVERAGEIF’s criterion for a number can be written bare (2023) or quoted ("2023"); both work.

Variant 2B

(a) How many rows report a lentil yield (i.e., how many non-blank cells in the Lentils column across all years)? (Hint: COUNT.)

(b) What is the average barley yield across all years in RM 100? (Use AVERAGEIF on the RM column.)

(c) How many rows have canola above 40 AND spring wheat above 45, in the same RM-year? (Use COUNTIFS.)

(d) Use a lookup to find the spring wheat yield in RM 100 in 2023.

(e) In how many rows is the canola yield above the overall average canola yield? (Use COUNTIF with the computed average.)

Answers
Part Value Formula
(a) Lentil count (snapshot value) =COUNT(I2:I10650)
(b) Avg barley, RM 100 (snapshot value) =AVERAGEIF(B2:B10650, 100, F2:F10650)
(c) Canola>40 & wheat>45 (snapshot value) =COUNTIFS(E2:E10650, ">40", C2:C10650, ">45")
(d) RM 100, 2023 wheat ≈ 52.9 bu/ac =XLOOKUP(100&"|"&2023, B2:B10650&"|"&A2:A10650, C2:C10650)
(e) Above-average count (snapshot value) =COUNTIF(E2:E10650, ">"&AVERAGE(E2:E10650))

Notes:

  • For (e), the ">"&AVERAGE(...) trick builds the criterion text dynamically. You cannot write ">AVERAGE(E2:E10650)" — that is treated as literal text.
  • For (a), use COUNT (numbers only), which naturally skips the many blanks in the Lentils column.

Type 3: PivotTables

Use the long-format file for these questions: rm_yields_1990plus_long.csv. Because the crop name is in its own Crop column, you can put Crop on Rows and pivot by crop — something the wide file cannot do in a single PivotTable (see Wide vs. Long Data). Create each PivotTable on a new sheet.

Variant 3A — Comparing crops

(a) Build a PivotTable of average yield for each crop in 2023 (put Year in Filters = 2023, Crop on Rows, Yield in Values → Average). Rank the crops from highest to lowest average yield.

(b) Why does Lentils appear far larger than every other crop? What does that tell you about mixing it into the comparison?

(c) Add Unit to the Filters and set it to bu/ac (excluding lentils). Now which crop has the highest average yield, and which the lowest?

Answers

Setup: Year → Filters (set to 2023); Crop → Rows; Yield → Values, set to Average (right-click the value → Summarize Values By → Average).

Part (a): Average yield by crop, 2023 (snapshot values):

Crop Avg yield Unit
Lentils ≈ 1314 lb/ac
Oats ≈ 71.1 bu/ac
Barley ≈ 54.6 bu/ac
Spring Wheat ≈ 42.9 bu/ac
Peas ≈ 34.0 bu/ac
Canola ≈ 33.9 bu/ac
Durum ≈ 29.6 bu/ac
Flax ≈ 20.3 bu/ac

Part (b): Lentils are reported in lb/ac, while every other crop is in bu/ac. The number is bigger because the unit is different, not because lentils out-yield everything — a classic reminder to check units before comparing. It should not be ranked against the bu/ac crops.

Part (c): Filtering to bu/ac only, Oats has the highest average (≈71 bu/ac) and Flax the lowest (≈20 bu/ac) in 2023.

Why long format matters here: “average yield by crop” requires Crop to be a single field you can drop on Rows. In the wide file the crops are eight separate columns, so this pivot is impossible without reshaping first.

Variant 3B — Crops over time (the drought signal)

(a) Build a PivotTable with Year on Rows and Crop on Columns, Yield in Values → Average. Filter Unit to bu/ac. Read across a recent row: in a typical year, which crops sit highest and lowest?

(b) Using the same table, compare canola in 2021 vs 2023. How large is the drought gap?

(c) Looking down the columns, which single year stands out as a drought year — yields low across all crops at once?

Answers

Setup: Year → Rows, Crop → Columns, Yield → Values (Average); Unit → Filters set to bu/ac.

Part (a): In a typical recent year, Oats and Barley sit highest, the wheats and peas/canola in the middle, and Flax lowest — the same ordering the by-crop pivot showed.

Part (b): Canola averaged ≈ 21.9 bu/ac in 2021 versus ≈ 33.9 bu/ac in 2023 — roughly a one-third drop in the drought year. The crop × year grid makes the gap jump out.

Part (c): 2021 stands out — reading across that row, every crop’s average dips together, the signature of a widespread drought rather than a crop-specific problem. (Contrast with a year where only one crop is low, which would point to a pest or disease specific to that crop.)

Note on units: always filter to a single Unit before comparing crops on one chart or table, so lentils (lb/ac) don’t swamp the bu/ac crops.

Type 4: Charts

Create the following charts on a new sheet in your workbook. Each chart should have a descriptive title, labelled axes, and be formatted so that someone unfamiliar with the data can understand what it shows.

Variant 4A — Distribution of yields

Filter the data to Year = 2023.

(a) Create a histogram of Canola yield across all RMs in 2023. Use approximately 10–15 bins.

Answer the following based on your histogram:

  • Is the distribution roughly symmetric, left-skewed, or right-skewed?
  • Are there any obvious outliers (very low-yielding RMs)?
  • What is the approximate centre of the distribution?

(b) Create a box-and-whisker plot comparing Canola, Spring Wheat, and Barley yields in 2023 (three boxes on one chart).

Answer the following based on your box plot:

  • Which crop has the highest median yield?
  • Which crop has the most variability (widest box / whiskers)?
  • Are outliers flagged for any crop?
Answers

Part (a) — Histogram:

How to create: Select the 2023 Canola column → Insert → Statistical Chart → Histogram. Right-click the horizontal axis → Format Axis → set the bin width to spread the ~3–60 bu/ac range across 10–15 bins.

Formatting: Title like “Distribution of Canola Yields Across Saskatchewan RMs, 2023”; x-axis “Canola yield (bu/ac)”, y-axis “Number of RMs”.

Expected interpretation:

  • The distribution is slightly left-skewed — most RMs cluster in the 30s–40s, with a tail of low-yielding (drought-affected) RMs pulling to the left.
  • A few RMs at the low end (single digits to ~15 bu/ac) look like outliers — real, drought-hit municipalities.
  • The centre is around 34–36 bu/ac (mean ≈33.9, median ≈35.5).

Part (b) — Box plot:

How to create: Arrange the three crop columns (2023 only) side by side and select them → Insert → Statistical Chart → Box and Whisker.

Formatting: Title like “2023 Yield Distributions by Crop, Saskatchewan RMs”; label the axes.

Expected interpretation:

  • Barley typically has the highest median yield of the three; canola the lowest.
  • Spring wheat tends to show wide spread (large IQR); all three show low-end outliers from drought-hit RMs.
  • Outliers appear as individual points beyond the whiskers — expect several at the low end.

Common mistakes:

  • Unlabelled axes or missing title — always include both.
  • Too few or too many histogram bins. Fewer than ~6 hides the shape; more than ~20 adds noise.
  • For the box plot, forgetting to select all three crop columns — you would get one box instead of three.

Variant 4B — A crop over time

Use the full dataset (all years). Either file works here: from the wide file put Canola in Values; from the long file put Yield in Values and filter Crop to Canola. Both give the same by-year averages.

(a) Build a PivotTable of average canola yield by year (Year on Rows, canola yield as Average), then create a line chart of average canola yield from 1990 to 2025.

Answer the following based on your line chart:

  • Is there a long-run upward trend in average canola yield? Why might that be?
  • Which years show sharp dips? What do those dips correspond to?

(b) On the same idea, make a line chart of average spring wheat yield by year. Do wheat and canola tend to rise and fall together across years?

Answers

Part (a):

How to create: PivotTable (Year → Rows, Canola → Values as Average) → select the result → Insert → Line Chart (or use a PivotChart). Title “Average Saskatchewan Canola Yield by Year, 1990–2025”; x-axis “Year”, y-axis “Average yield (bu/ac)”.

Expected interpretation:

  • Yes — there is a clear long-run upward trend. Average canola yields have roughly risen over the decades, driven by better genetics (hybrids), agronomy, and equipment.
  • Sharp dips appear in known drought years — most notably 2021, and others earlier in the series. These are weather shocks, not trend changes.

Part (b):

  • Wheat and canola yields largely move together year to year — a good year for one tends to be a good year for the other, because both respond to the same growing-season weather. The overlaid line charts should rise and fall roughly in sync, with drought years (like 2021) dipping for both.

Common mistakes:

  • Plotting every RM instead of the yearly average — you want the PivotTable’s per-year average, not 10,000 points.
  • Leaving the year axis as a “count” or treating years as categories unevenly — make sure the x-axis is the Year field.

Auto-Generated Practice Quiz

For a randomized practice quiz that draws one question from each type, visit the Practice Quiz Generator. Each time you click Generate quiz, you get a fresh set of four questions — just like the real module test.