Free preview · Lesson 1
Formulas and Cell References
Writing formulas
Every formula starts with =. Use cell references instead of typing numbers so the sheet updates automatically: =B2*C2 instead of =500*3.
Relative, absolute and mixed references
- A1 (relative) – both column and row shift when copied.
- $A$1 (absolute) – never changes. Use it for a fixed rate such as a GST % cell.
- $A1 – column fixed, row changes. A$1 – row fixed, column changes.
- Press F4 while editing a reference to cycle through these.
Example: C1 contains =A1*$B$1. Copied down to C2 it becomes =A2*$B$1 – the price row moves, the fixed rate stays.
Everyday functions
- SUM, AVERAGE, MIN, MAX, COUNT (numbers only), COUNTA (non-empty cells)
- ROUND(value, 2) for currency
- TODAY() and NETWORKDAYS(start, end) for date calculations
- CONCAT or the & operator to join text: =A2&" "&B2
IF and its friends
- =IF(B2>=60,"Pass","Fail") – a mark of 59.5 returns Fail.
- Combine conditions: =IF(AND(B2>=60,C2>=75),"Eligible","Not eligible").
- IFS for many bands: =IFS(B2>=90,"A",B2>=75,"B",TRUE,"C").
- IFERROR(formula,"Not found") hides error values in reports.
Common error values
- #N/A – lookup value not found
- #DIV/0! – division by zero
- #VALUE! – wrong data type (text in a maths formula)
- #REF! – a referenced cell was deleted
- #NAME? – misspelt function name