ETF1100 Chap.1 Data Foundations and Excel
Data Foundations and Excel
Data Foundations and Excel establishes the decisions that come before a formula: identify what one row represents, classify each variable, separate identifiers from measurements, define the target population and keep a reproducible spreadsheet structure. The chapter shows why a numerical code can still be categorical, why missing is not zero, and why conclusions inherit the scope of the recorded observations.
What this chapter covers
- 01
Observational units and rows
- 02
Categorical and numerical variables
- 03
Nominal and ordinal categories
- 04
Discrete and continuous measurements
- 05
Identifiers, dates and missing values
- 06
Tidy Excel structure and audit checks
Worked example · free
Audit a transaction file before analysis
- 1Define one row as an order, not necessarily one unique customer.
- 1Treat OrderID as an identifier, Channel as nominal and LoyaltyTier as ordinal.
- 1Treat Items as discrete numerical and Spend as numerical.
- 1Subtract the two dates to create a delivery-duration measurement.
- 1Limit conclusions to recorded orders because no customer identifier separates repeat buyers.
Key terms
- Observational Unit
- The entity represented by one row, such as an order, customer or call.
- Nominal Variable
- A categorical variable whose labels have no inherent order.
- Ordinal Variable
- A categorical variable with ordered levels but no guaranteed equal spacing.
- Missing Value
- An unavailable observation that must not be silently treated as zero.
Data Foundations and Excel FAQ
Why is a postcode categorical if it contains numbers?
The digits identify an area rather than measure an amount. Arithmetic on postcodes has no useful meaning, so the variable is nominal categorical. Storage format does not determine statistical type; the role of the values in the business question does.
What should I check before using an Excel function?
Define the observational unit, inspect variable types, count missing values, check identifiers for duplicates and confirm dates or numbers are stored correctly. Preserve the original file and create derived variables in new columns so each transformation remains auditable.
Can a dataset have more than one possible unit?
Yes, but an analysis must choose one clearly. A transaction file can be grouped to customers when a stable customer identifier exists. Without that identifier, customer-level claims are unsupported even if the file contains names or repeated purchase patterns.
How can I check that a cleaned file is still auditable?
Reconcile row counts before and after every filter, preserve stable identifiers and record each correction or derived field in a transformation log. Keep the untouched source separately. A reviewer should be able to trace a reported statistic back to eligible source rows and understand every exclusion without guessing.
Exam move
Begin every practice problem by writing three lines: one row represents, the target variable is, and the intended population is. Build a small classification table for categorical versus numerical, then divide categorical into nominal and ordinal and numerical into discrete and continuous. Add a warning beside identifiers because numbers used only as labels should not be averaged.
In Excel, preserve a raw tab, place one variable in each column, use one header row and avoid merged cells inside the data range. Filter for blanks and impossible values before calculating. When dates appear, decide whether they label time or should be converted into a duration.
Practise translating a business request into the correct analysis unit, especially when rows are calls, visits or transactions rather than unique people. Finish each answer with a scope sentence that names what the data can represent and what repeated observations or missing fields prevent you from claiming.
Build a data dictionary for one unfamiliar spreadsheet: list the field name, plain-language meaning, unit, allowable values, missing-value code and analytical role. Compare the dictionary with the actual cells and note any mismatch, such as a numeric amount stored as text, an unexpected category spelling or a date outside the collection period.
Then create an audit column for each important rule instead of silently changing the source. Count how many rows fail each rule and decide whether correction, exclusion or a separate category is defensible. Practise distinguishing a duplicate record from a legitimate repeat transaction; identical values do not prove duplication without a stable record key and process knowledge.
Reconcile row counts after every filter so later tables can be traced back to the landed file. For a derived duration, test a few rows manually, confirm the unit and investigate negative or implausibly large values. When summarising by group, verify that blank categories are either shown explicitly or excluded with a stated denominator. End the audit by saving a clean analysis copy and a short transformation log.
This workflow makes the spreadsheet reproducible and gives you evidence for any limitation you report. In an exam scenario, compress it to four checks: unit of observation, variable role, data-quality risk and target population.
Working through Data Foundations and Excel in ETF1100? Sia is AskSia’s AI Statistics tutor — ask any ETF1100 Data Foundations and Excel question and get a clear, step-by-step explanation grounded in how ETF1100 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
