Tutorial 1: Formulas, References, and Data Quality
COMP210 · Unit 3
Objectives
Write formulas with relative and absolute references.
Separate raw data, calculations, and presentation.
Detect common spreadsheet errors.
A spreadsheet cell can contain a value, label, or formula. Formulas express relationships and should be copied carefully. Relative references change when copied; absolute references such as $B$2 stay fixed. Keep source data in a consistent table with one record per row.
=B2*C2
=SUM(D2:D20)
=D2/$B$1
Data quality problems include missing values, inconsistent units, duplicate rows, and formulas overwritten by constants. Use validation, named ranges, checks, and clear units. A chart cannot repair incorrect source data.
Exercises
Build a budget with quantities, prices, tax, and total.
Copy a formula using one fixed tax-rate cell.
Find and document three data-quality checks.
Self-check
What is an absolute reference?
Why separate raw data?
What does SUM calculate?
Self-Check Quiz
1. Which reference stays fixed when copied?
AnswerAn absolute reference such as $B$2.
2. Should a chart be trusted without checking source data?
AnswerNo. Visual output inherits missing, duplicated, or incorrect source data.
Homework
Create a monthly budget workbook.
Add validation and an error-check area.
Explain every formula and fixed reference.
Sample answerA sound workbook separates inputs, calculations, and report sheets; uses absolute references for shared rates; validates numeric ranges; flags missing or negative values; and documents assumptions and formulas so another person can audit it.