Chapter 2 — Practice Demo Lab: Reference Solution

Lab: Clean and summarize the Ontario forest file together

Note

Reference solution for the Chapter 2 Practice Demo Lab (Tasks 1–5 of Lab: Clean and summarize the Ontario forest file together). The graded Canvas lab has its own private answer key.

Dataset on_forest_statistics_1.xlsx (Data sheet): 179 rows × 8 columns. Key Excel columns — D SERAL_NAME, F PFTName (forest type), H SumOfTotalHa (area, ha). Data rows 2–180.

Task 1 — Save a working copy

File → Save As on_forest_statistics_1_clean.xlsx into your outputs/ folder; leave the raw file in data/ untouched. (No formula — this is the raw-vs-working-copy discipline.)

Task 2 — Make it a structured table

Click any cell in the data → Insert → Table (tick “My table has headers”). With the table selected, Table Design → Table Name → ontario_data. Autofilter dropdown arrows should now appear in the header row. Structured references (e.g., ontario_data[SumOfTotalHa]) now work in formulas.

Task 3 — Add a size_class column

In a new column, classify SumOfTotalHa with thresholds 1,000 and 50,000:

=IFS([@SumOfTotalHa]<1000, "small",
     [@SumOfTotalHa]<50000, "medium",
     TRUE, "large")

Then count each class with COUNTIF, e.g. =COUNTIF(ontario_data[size_class], "small"). Expected counts:

size_class count
small 50
medium 69
large 60

Task 4 — One conditional aggregation

Worked example (the always-computable option): count of rows where SumOfTotalHa > 100,000 ha.

=COUNTIF(ontario_data[SumOfTotalHa], ">100000")

Result: 35 rows. Spot check: sort the column largest-to-smallest and confirm that many values sit above 100,000. ✔

(If your group instead chose SUMIF for a named forest type or AVERAGEIF for Mature, the same idea applies — write the condition on PFTName or SERAL_NAME and spot-check against a sorted view.)

Task 5 — Submit

Hand in a one-page worksheet with your answers to Tasks 1–4, or a screenshot of the cleaned workbook showing the structured table and the size_class column — with all group members’ names. (Graded lab: follow Canvas.)