Module 1 - Test bank
The test bank
This bank holds 40 questions: ten in each section.
- Section 1 covers basic Excel operations
- Section 2 covers basic descriptive statistics in Excel
- Section 3 covers Conditional functions and lookup tables
- Section 4 covers PivotTables
There are three datasets used in the test bank. The datasets are:
- Saskatchewan RM Crop Yields (1990–2025). Average yield (bu/ac) for eight crops in every Rural Municipality.
- Saskatchewan RM Crop Yields (Long) (1990–2025). Long format of the Saskatchewan RM Crop Yields dataset, useful for PivotTables.
- Manitoba Wheat Variety (2020–2025). Red spring wheat yield by variety and municipality.
The format of your test
The module test will contain one question per section. The test will only use one of the datasets, and the dataset will be provided on the test.
Here is an example of what a test will look like and the expectation for what your workbook will look like:
- Example test (PDF) – four questions, one per section, drawn from this bank; Questions 2-4 use the Saskatchewan RM Crop Yields dataset.
- Example workbook (XLSX) – a completed submission: one labelled worksheet per question, with a live PivotTable for Question 4.
- At least an hour before the test, “Schedule an appointment” in the test for that day.
- Bring your (charged) laptop to the test.
- Your test will be accessible at 11:30am.
- When you get to the room:
- Close all applications other than your webbrowser (used to access the test) and Excel.
- Log into zoom and join the breakout room with your name on it.
- In Zoom, share your screen (not an application window, but your whole screen) and record your session.
- Navigate to Canvas and access your test.
- Download the dataset that you need
- Complete the test in Excel.
- Upload the completed workbook to Canvas (while still logged into zoom).
- Leave zoom, and upload the screenshot recording to Canvas.
- Your done!!
Section 1 — Building a Worksheet
These questions are about spreadsheet mechanics: anchoring references, formatting numbers and dates, order of operations, and the difference between changing a value and changing how it looks. The data is given in the question — copy the table and paste it into Excel.
Question 1
Copy this table into a blank sheet: select it, copy, and paste into cell A1.
| Field | Acres | Yield (bu/ac) |
|---|---|---|
| Kestrel | 120 | 41.2 |
| Meadowvale | 240 | 38.6 |
| Nightjar | 95 | 44.8 |
| Home | 310 | 36.1 |
| Rented | 175 | 39.9 |
Off to the right of the table, in a column with nothing else in it, put the canola price $14.20 in a single labelled cell.
(a) [4 marks] Add a column that calculates revenue per acre (yield X price) for each field using relative and absolute references. Format as currency with two decimals.
(b) [3 marks] Add another column that calculates total revenue per field. Format as currency a thousands separator and no decimals.
(c) [2 marks] Under the table, use SUM to total the acres, the total bushels (yield × acres), and the total revenue.
(d) [1 mark] Explain why we need to include a $ in the price reference in part a but not in part b.
Answer
Question 2
Copy this table into a blank sheet: select it, copy, and paste into cell A1. The table contains yields of three crops in each field in bushels per acre.
| Field | Canola | Wheat | Barley |
|---|---|---|---|
| North | 41.2 | 52.4 | 63.1 |
| South | 38.6 | 49.8 | 58.7 |
| Creek | 44.8 | 55.2 | 66.4 |
| Home | 36.1 | 47.3 | 61.9 |
| Rented | 39.9 | 51.0 | 60.2 |
Enter the prices in a single row: Canola $14.20, Wheat $8.35, Barley $5.60.
(a) [4 marks] Calculate the revenue per acre for each crop and field using mixed referencing (this would be three new columns). This involves writing one formula and copying through all of the other cells.
(b) [2 marks] State the formula you used, and say which part of the price reference is locked and which is not.
(c) [1 mark] What would happen to your formula if you used an absolute reference (e.g., $A$8 where canola price was saved in cell A8) to refer to prices?
(d) [2 marks] Under your revenue block, use AVERAGE to find the mean revenue per acre for each crop.
(e) [1 mark] Which crop gives the highest average revenue per acre, and by how much over the second-placed crop?
Answer
Question 3
Copy this table into a blank sheet: select it, copy, and paste into cell A1. The table contains the protein content of five wheat loads, recorded as proportions.
| Load | Protein |
|---|---|
| A | 0.1342 |
| B | 0.1285 |
| C | 0.1401 |
| D | 0.1198 |
| E | 0.1356 |
(a) [4 marks] Copy the protein column into a new column and format it as a percentage.
(b) [3 marks] Compute the average protein using both the column in general format and the column formatted as a percentage.
(c) [2 marks] The contract pays a premium on loads above 13.5% protein. Use IF to flag the qualifying loads and COUNTIF to count them.
(d) [1 mark] Explain why your condition in part c had to compare against 0.135 rather than 13.5, even though the column reads 13.42%.
Answer
Question 4
Copy this table into a blank sheet: select it, copy, and paste into cell A1. The table contains canola yields from seven trial plots.
| Plot | Yield (bu/ac) |
|---|---|
| A | 47.2 |
| B | 61.8 |
| C | 39.5 |
| D | 55.1 |
| E | 43.7 |
| F | 58.9 |
| G | 50.3 |
(a) [4 marks] Summarise the yield column with MIN, MAX, AVERAGE, and MEDIAN.
(b) [3 marks] Add the range.
(c) [2 marks] Copy the yield into a new column. In this column, update plot B to 91.8 bu/ac. Calculate the statistics again and report which ones have changed.
(d) [1 mark] Explain why the median moved so little compared to the mean.
Answer
Question 5
Copy this table into a blank sheet: select it, copy, and paste into cell A1. The table contains the moisture reading of six truckloads of wheat.
| Truck | Moisture (%) |
|---|---|
| Truck 1 | 13.8 |
| Truck 2 | 14.6 |
| Truck 3 | 15.2 |
| Truck 4 | 13.1 |
| Truck 5 | 14.9 |
| Truck 6 | 16.0 |
(a) [4 marks] The elevator’s dry limit is 14.5% – if the percentage is above this it is labelled as wet. Put the limit in a cell of its own and add a column that labels each truck Dry or Wet, using IF with a reference to the limit cell.
(b) [3 marks] Use COUNTIF and COUNT to calculate the fraction of loads that are wet. Format it as a percentage.
(c) [3 marks] Add a new column that calculates the fraction of loads that would be wet if the limit was lowered to 13.5%.
Answer
Question 6
Copy this table into a blank sheet: select it, copy, and paste into cell A1. The table contains the seeding and harvest dates for five fields.
| Field | Seeded | Harvested |
|---|---|---|
| Sandhill | 2026-05-08 | 2026-09-14 |
| Tamarack | 2026-05-21 | 2026-09-18 |
| Upland | 2026-04-29 | 2026-09-09 |
| Verdant | 2026-05-19 | 2026-10-02 |
| Westgate | 2026-05-12 | 2026-09-22 |
(a) [3 marks] Add a column that calculates each field’s days to harvest (harvest date minus seeding date). Format it as a plain number.
(b) [3 marks] Use MIN and MAX to find the shortest and longest growing periods, and name the fields they belong to.
(c) [2 marks] Compute the average growing period.
(d) [2 marks] Suppose a classmate was working with similar data, with a date that looks like 04-May-1900. She says that subtracting dates doesn’t work for her – how would you advise her to fix this problem?
Answer
Question 7
Copy this table into a blank sheet: select it, copy, and paste into cell A1. The table contains one day’s canola sales by grade: the bushels delivered and the price received for each.
| Grade | Bushels | Price ($/bu) |
|---|---|---|
| No. 1 | 1240 | 14.85 |
| No. 2 | 860 | 13.40 |
| Sample | 410 | 10.75 |
(a) [4 marks] Add a column that calculates the revenue for each grade (bushels X price). Total the revenue and the bushels with SUM.
(b) [3 marks] Calculate the blended price per bushel the farm actually received (total revenue over total bushels).
(c) [2 marks] Calculate the same blended price in a single cell using SUMPRODUCT and SUM (e.g., calculating a weighted average).
(d) [1 mark] Compare your blended price with AVERAGE of the price column. Explain which one describes what the farm received, and why the two differ.
Answer
Question 8
Copy this table into a blank sheet: select it, copy, and paste into cell A1. The table contains a simple crop budget: the expected yield, price and input cost for three crops.
| Crop | Yield (bu/ac) | Price (\(/bu) | Cost (\)/ac) | |
|---|---|---|---|
| Canola | 42 | 14.20 | 415 |
| Wheat | 55 | 8.35 | 320 |
| Barley | 68 | 5.60 | 285 |
(a) [4 marks] Add a column that calculates each crop’s revenue per acre (yield X price).
(b) [3 marks] Add a column that calculates each crop’s profit per acre (revenue minus cost).
(c) [3 marks] Add a column that calculates each crop’s profit margin (profit over revenue). Format it as a percentage.
Answer
Question 9
Copy this table into a blank sheet: select it, copy, and paste into cell A1. The table contains the acres each field seeded to three crops.
| Field | Canola | Wheat | Peas |
|---|---|---|---|
| North | 320 | 160 | 80 |
| South | 160 | 320 | 160 |
| Creek | 240 | 80 | 120 |
| Home | 160 | 240 | 80 |
| Rented | 80 | 160 | 160 |
(a) [4 marks] Add a column that totals each field’s seeded acres with SUM.
(b) [3 marks] Add a row that totals each crop’s acres.
(c) [2 marks] In a single cell, total all the acres with one SUM over the whole block of numbers.
(d) [1 mark] What would =SUM(B2:B6, D2:D6) give instead? Which acres would it be missing?
Answer
Question 10
Copy this table into a blank sheet: select it, copy, and paste into cell A1. The table contains the tonnes delivered by seven grain trucks. Two cells are deliberately empty and one holds text.
| Load | Tonnes |
|---|---|
| Load 1 | 24.8 |
| Load 2 | |
| Load 3 | 31.2 |
| Load 4 | rejected |
| Load 5 | 28.6 |
| Load 6 | |
| Load 7 | 26.4 |
(a) [4 marks] Use COUNT, COUNTA, and SUM on the tonnes column and report the three results.
(b) [3 marks] Compute the average tonnes per load.
(c) [2 marks] Seven trucks were dispatched. Explain what each of your counts in part a counted, why none equals seven, and which loads AVERAGE used.
(d) [1 mark] Explain why COUNT and COUNTA give different results.
Answer
Section 2 — Descriptive Statistics and Distributions
Saskatchewan RM Crop Yields
Question 11
This question uses: rm_yields_1990plus.csv
Filter the data to 2023. Then copy the Canola yields into a new worksheet.
(a) [4 marks] Compute the mean and the median canola yield.
(b) [2 marks] 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?
(c) [2 marks] Compute the standard deviation and the interquartile range (IQR) of the same column.
(d) [2 marks] Would the mean or the median better represent a typical RM here? Justify your choice using your answers to parts a-c.
Answer
Question 12
This question uses: rm_yields_1990plus.csv
Filter the data to 2023. Then copy the Canola and Barley yields into a new worksheet.
(a) [4 marks] Compute the mean and standard deviation of each crop’s yield.
(b) [1 mark] 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?
(c) [2 marks] Compute the coefficient of variation (CV) for each crop.
(d) [2 marks] Using the CVs, state which crop’s yields were more variable relative to their average.
(e) [1 mark] Write one sentence, suitable for a farm newspaper, that correctly compares the variability of the two crops without misleading the reader.
Answer
Question 13
This question uses: rm_yields_1990plus.csv
Create two new worksheets, one with canola yields for 2019 (a normal moisture year in most of the province) and one with canola yields for 2021 (a drought year in most of the province). You can do this by filtering the data to each year in turn and copying the canola yields into a worksheet of its own.
(a) [5 marks] Compute the mean and median canola yield for each of the two years.
(b) [3 marks] Compute the standard deviation and CV for each year.
(c) [2 marks] Which year would you say had more variable canola yields across RMs? Justify your answer using the statistics you computed.
Answer
Question 14
This question uses: rm_yields_1990plus.csv
Filter the data to 2023. Then copy the Oats yields into a new worksheet.
(a) [4 marks] Compute the mean, median, and the 90th percentile of oat yield.
(b) [3 marks] State in plain language what the 90th percentile value tells you about RMs that year.
(c) [2 marks] Compute Q1, Q3, and the IQR.
(d) [1 mark] Compare the mean and median from part a and say whether the distribution is left-skewed, right-skewed, or roughly symmetric.
Answer
Question 15
This question uses: rm_yields_1990plus.csv
Filter the data to 2024. Then copy the Spring Wheat yields into a new worksheet.
(a) [4 marks] Calculate the 10th, 50th and 90th percentiles of spring wheat yield (PERCENTILE.INC).
(b) [2 marks] Explain what the 10th percentile value means for an RM sitting at it.
(c) [2 marks] Calculate the gap between the 90th and the 10th percentile.
(d) [2 marks] Calculate the range of yields and compare it to the gap between the 90th and 10th percentiles. What do you think is a better measure of the spread of yields across RMs, and why?
Answer
Manitoba Wheat Variety
Question 16
This question uses: mb_wheat_reported_2020_2025.csv
(a) [4 marks] Compute the mean and the median of the yields.
(b) [2 marks] Compare the two values, and say whether their relationship implies left skew, right skew, or symmetry of the yield distribution.
(c) [2 marks] Compute the standard deviation, the IQR, and the coefficient of variation.
(d) [2 marks] Suppose 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.
Answer
Question 17
This question uses: mb_wheat_reported_2020_2025.csv
Filter the data to 2021 and again to 2023, copying each year’s yields into a worksheet of its own (MEDIAN and STDEV.S have no conditional versions, so each year needs its own copy of the data).
(a) [4 marks] Compute the mean and median yield for each of the two years.
(b) [3 marks] Compute the standard deviation and CV for each year.
(c) [2 marks] 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) [1 mark] Compare the CVs of the two years and say whether the drought year was also more variable in relative terms.
Answer
Question 18
This question uses: mb_wheat_reported_2020_2025.csv
Filter the data to the single variety AAC BRANDON (BW 932) (the most widely grown).
(a) [4 marks] Compute the mean, median, and standard deviation of this variety’s yields.
(b) [3 marks] Compute its CV.
(c) [3 marks] The CV of all wheat yields combined (every variety) is about 0.20. Compare this single variety’s CV to that all-varieties figure. Is a single variety more or less variable than the whole mix of varieties, and does that make sense?
Answer
Question 19
This question uses: mb_wheat_reported_2020_2025.csv
Compare two specific varieties across their yields: SY MANNESS and AAC BRANDON (BW 932).
(a) [4 marks] Compute the mean yield of each variety.
(b) [3 marks] Compute the standard deviation of each.
(c) [2 marks] Compute the coefficient of variation of each.
(d) [1 mark] Which variety do you think is riskier for a grower to choose, and why? Use the statistics you computed to justify your answer.
Answer
Question 20
This question uses: mb_wheat_reported_2020_2025.csv
(a) [4 marks] Compute the plain average of the yield column with AVERAGE.
(b) [3 marks] Compute the acreage-weighted average yield in a single cell (SUMPRODUCT of yield and acres, divided by SUM of acres).
(c) [2 marks] Compare the two averages and state which is higher.
(d) [1 mark] Explain which average better describes the yield of a typical Manitoba wheat acre, and why the two numbers can differ.
Answer
Section 3 — Conditional Functions & Lookups
Saskatchewan RM Crop Yields
Question 21
This question uses: rm_yields_1990plus.csv
(a) [5 marks] Use a two-criteria lookup (XLOOKUP on a combined RM-and-Year key) to pull the canola yield for RM 1 in 2023.
(b) [3 marks] Do the same to pull the spring wheat yield for RM 100 in 2023.
(c) [2 marks] 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
Question 22
This question uses: rm_yields_1990plus.csv
(a) [5 marks] Use COUNTIFS to count how many RM-year rows have canola above 40 AND spring wheat above 45 in the same row.
(b) [3 marks] Use COUNTIF to count rows with canola above 40 (ignoring wheat).
(c) [2 marks] Explain why the count in (a) is smaller than the count in (b).
Answer
Question 23
This question uses: rm_yields_1990plus.csv
(a) [5 marks] Write a function using AVERAGEIF that calculates the average yield of canola in 2015. In the cell below calculate the average yield in 2019.
(b) [3 marks] Use AVERAGEIF to compute the average barley yield for the same two years.
(c) [2 marks] Compare the absolute and percentage change in canola yield from 2015 to 2019 with the change in barley yield over the same period. Which crop experienced higher growth in yield over this period?
Answer
Question 24
This question uses: rm_yields_1990plus.csv
(a) [4 marks] Write a nested IF 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) [3 marks] Use COUNTIF to count how many cells fall in each of the three labels.
(c) [2 marks] Use COUNTIFS to count how many cells fall into each of the three labels in 2023.
(d) [1 mark] What do the answers to (b) and (c) tell you about canola yields in 2023 compared to the whole dataset?
Answer
Question 25
This question uses: rm_yields_1990plus.csv
(a) [4 marks] Use AVERAGEIF to compute the average canola yield for the year 2021 and for 2023.
(b) [3 marks] Use COUNTIFS to count, within 2021 only, how many RMs had canola below 20 bu/ac.
(c) [2 marks] Use COUNTIFS to do the same for 2023.
(d) [1 mark] Compare the two counts and explain what the difference says about how the two years differed at the low end.
Answer
Manitoba Wheat Variety
Question 26
This question uses: mb_wheat_reported_2020_2025.csv
(a) [4 marks] Use COUNTIF to count how many yields exceed 40 bu/ac.
(b) [3 marks] Use COUNTIF to count how many exceed 50, 60, 70, 80, and 90 bu/ac.
(c) [3 marks] Divide each count by the total count. Explain how these fractions relate to what is shown in a histogram.
Answer
Question 27
This question uses: mb_wheat_reported_2020_2025.csv
(a) [4 marks] Use AVERAGEIF to compute the average yield for SY MANNESS and for AAC BRANDON (BW 932).
(b) [2 marks] Use COUNTIF to count the rows behind each average.
(c) [2 marks] State which variety has the higher average, and which has more data.
(d) [1 mark] Explain why the higher-average variety does not necessarily have more data behind it, using your two counts.
(e) [1 mark] State, in one sentence, why you would report both the average and the count when comparing varieties.
Answer
Question 28
This question uses: mb_wheat_reported_2020_2025.csv
(a) [6 marks] Use COUNTIFS to count the rows for AAC BRANDON (BW 932) in each year 2020 through 2025.
(b) [2 marks] State which year has the most AAC BRANDON results.
(c) [2 marks] What types of farmers do you think stopped growing Brandon over these years? What do you think this implies for Brandon’s average yields?
Answer
Question 29
This question uses: mb_wheat_reported_2020_2025.csv
(a) [4 marks] Use SUMIFS to total the AAC BRANDON (BW 932) and SY MANNESS acres in the years 2020-2025 only.
(b) [3 marks] Use AVERAGEIFS to compute their average yields in the same time period.
(c) [3 marks] Does your finding in part b explain your finding in part a?
Answer
Question 30
This question uses: mb_wheat_reported_2020_2025.csv
(a) [4 marks] Use COUNTIFS to count how many times AAC BRANDON (BW 932) yielded above 60 bu/ac in 2023 (conditions on the variety, the year and the yield together).
(b) [3 marks] Use COUNTIFS to count the variety’s 2023 rows in total, and divide to get the fraction of its 2023 results above 60. Format it as a percentage.
(c) [2 marks] Repeat parts a and b for SY MANNESS.
(d) [1 mark] Which variety cleared 60 bu/ac more often in 2023?
Answer
Section 4 — PivotTables
PivotTables are marked partly on presentation: label your rows and columns clearly and set the right aggregation (Average, Sum, or Count). Your final workbook must leave every requested result visible. If a question needs a second view, create a second PivotTable rather than changing one you still need as an answer.
Saskatchewan RM Crop Yields (long file)
For this section use the long file rm_yields_1990_2025.csv, which has the crop name in its own Crop column so you can group by crop.
Question 31
This question uses: rm_yields_1990_2025.csv
(a) [7 marks] Build a PivotTable showing each RM’s spring wheat yield in 2023, sorted from highest to lowest.
(b) [3 marks] In a cell beside the table, report the three highest-yielding RMs and their yields.
Answer
📗 Download the answer workbookQuestion 32
This question uses: rm_yields_1990_2025.csv
(a) [6 marks] Build a PivotTable showing the 2021 yields of Spring Wheat, Peas, and Canola in the southeast corner of the province (RMs 1, 2, 31, and 32).
(b) [4 marks] Did one RM have the highest yields for all three crops?
Answer
Question 33
This question uses: rm_yields_1990_2025.csv
(a) [6 marks] Build a PivotTable showing each crop’s average yield in each year after 2015.
(b) [4 marks] 2021 was a bad drought year – is this borne out in the data?
Answer
Question 34
This question uses: rm_yields_1990_2025.csv
(a) [7 marks] Build a PivotTable showing the number of RMs that reported each crop in 2023.
(b) [3 marks] Which crop was reported in the fewest RMs, and in how many?
Answer
Question 35
This question uses: rm_yields_1990_2025.csv
(a) [7 marks] Build a PivotTable showing the average yields of Durum and Spring Wheat since 2000.
(b) [3 marks] Compare the average yields in the first five years to yields in the last five years for both crops. What does this say about the trend in wheat yields over the last 20+ years?
Answer
Manitoba Wheat Variety
For this section use mb_wheat_reported_2020_2025.csv.
Question 36
This question uses: mb_wheat_reported_2020_2025.csv
(a) [7 marks] Build a PivotTable showing each variety’s average yield in 2024 and the number of municipalities reporting it.
(b) [3 marks] Which variety had the highest average yield, and how many municipalities is that number based on?
Answer
Question 37
This question uses: mb_wheat_reported_2020_2025.csv
(a) [7 marks] Build a PivotTable showing the total acres planted to each variety over 2020–2025.
(b) [3 marks] Which variety was planted on the most acres?
Answer
Question 38
This question uses: mb_wheat_reported_2020_2025.csv
(a) [7 marks] Build a PivotTable showing Manitoba’s average wheat yield in each year from 2020 to 2025.
(b) [3 marks] Which was the worst year for yields?
Answer
Question 39
This question uses: mb_wheat_reported_2020_2025.csv
(a) [6 marks] Build a PivotTable showing each municipality’s average wheat yield in 2024, sorted from highest to lowest.
(b) [4 marks] Report the top three municipalities and their yields in a sentence.
Answer
Question 40
This question uses: mb_wheat_reported_2020_2025.csv
(a) [6 marks] Build a PivotTable comparing the average yields of AAC BRANDON (BW 932) and AAC STARBUCK <SECAN> in each year from 2020 to 2025.
(b) [4 marks] Did the same variety outperform the other in every year?