This module covers the formula skills behind most spreadsheet tasks. You will build a formula that copies correctly, fix a cell with a dollar sign, decide with IF, look up a value from a table and repair a formula that broke after copying. Cambridge IGCSE ICT (0417) is a practical subject, so check the current syllabus page for the exact spreadsheet requirements.
All sheets, records and people in these lessons are invented practice material.
What should you know before starting?
You should be able to type in a cell, understand that columns are letters and rows are numbers, and write a simple sum. Nothing else is assumed. The lessons use generic features that exist in common spreadsheet software.
An orienting example
Mei Ling (an invented student) has a small stationery sheet.
Column B holds quantity, column C holds unit price and column D should hold the total. In D2 she types =B2*C2. With 10 pens at 1.50 each, D2 shows 15.00.
She copies D2 down. D3 becomes =B3*C3 and D4 becomes =B4*C4, because each reference moves with the row. That is a relative reference, and it is exactly what she wanted.
Next she adds a service charge rate of 0.06 in F1 and writes =D2*F1 in E2. Copied down, E3 becomes =D3*F2, and F2 is empty, so the answer is 0.
The rate must stay fixed, so the formula needs =D2*$F$1. Every lesson below builds one piece of that thinking.
In which order should you study the lessons?
- Build a formula with correct relative references: you must predict how a formula moves when copied.
- Use an absolute reference deliberately: the dollar sign fixes a cell that must not move.
- Choose an appropriate logical function: IF, AND and OR turn a rule into a result.
- Use a lookup with clearly stated assumptions: lookups only work when their conditions are true.
- Diagnose a formula copied into the wrong range: the skill that ties the other four together.
Then try the mixed practice set.
Common traps
- Typing numbers into a formula instead of referring to the cell that holds them.
- Forgetting the dollar signs on a fixed rate or a lookup table.
- Using
>when the rule says “at least”, so the boundary value is lost. - Leaving the final lookup argument out, so the program guesses a match type.
- Trusting a result because it looks like a number, without reading the formula.
How should you use the practice set?
Do the questions in order after the lessons. Cover each answer, write your own, then compare. Log each slip in the mistake log and retest it a few days later.
The spreadsheet reference practice grid lets you predict a copied formula and then check yourself, and the ICT practical task and evidence checker gives a quick pass over any task you have finished.
If your formulas keep breaking when you copy them, a teacher can watch you build one live in online one-to-one ICT tuition and show you where the reference moves.