Week 05 · lesson
A Spreadsheet Is a Data Model
A spreadsheet is not just a grid where numbers happen to fit. The grid represents a model of information.
A row usually represents one record: one device, one response, one transaction, one test run. A column represents a field or variable measured about those records. A cell is the intersection of a record and a field.
Read the structure before the values
Consider this fictional device table:
| Device | Battery % | Screen time min | Storage used GB | Apps installed |
|---|---|---|---|---|
| C-014 | 82 | 138 | 41 | 27 |
| C-021 | 55 | 214 | 58 | 35 |
| C-033 | 91 | 96 | 36 | 22 |
The first column is an identifier. The remaining columns are numeric measurements with units or meanings.
If one cell contains unknown, the spreadsheet has not stopped being a data model. It has gained a missing/uncertain value that must be handled deliberately.
Data type changes what operations make sense
You can average battery percentage. Averaging device IDs makes no useful sense even if the IDs contain numbers.
Useful categories for this course:
- identifier — names a record;
- number — supports arithmetic when units and meaning agree;
- category — labels a type or group;
- date/time — encodes temporal information;
- text — descriptive information;
- missing/unknown — absence that should not be silently converted into zero.
Worked failure: zero is not the same as missing
Suppose a device has no recorded screen-time value. Entering 0 says the device was measured and used for zero minutes. Leaving it blank or using a defined missing marker says the value is not available.
Those claims are different.
If five missing values are replaced with zeros, the calculated average can drop even though no device actually had zero minutes.
Build a Mini Data Dictionary
Use this synthetic schema:
| Field | Meaning | Type | Unit | Missing allowed? |
|---|---|---|---|---|
device_id | fictional device identifier | identifier | none | no |
battery_pct | battery at observation time | number | percent | yes |
screen_time_min | recorded use interval | number | minutes | yes |
storage_used_gb | occupied storage | number | GB | yes |
apps_installed | count of installed apps | number | count | yes |
Add two fields of your own that would be useful for a fictional device-management study. Do not add real student names or other personal information.
For each added field, explain why the type and unit match the question you want to answer.
Failure mode: collecting a field because “we might need it”
More columns are not automatically better. Every collected field creates storage, interpretation, privacy, and quality obligations. Week 7 will return to this when Forms creates the dataset.
Evidence to keep
Submit the completed Mini Data Dictionary and identify:
- one field that can be averaged;
- one field that should not be averaged;
- one way a missing value could be misinterpreted;
- one field you deliberately chose not to collect and why.
The goal is to see the model underneath the cells before you start writing formulas.