Each question gives you a small spreadsheet and asks you to work something out. Type a formula in the formula bar and press Check (or Enter): it's put in the answer cell, worked out, and checked. Any formula that gives the right answer passes, as long as it still works when the data changes.
- Click a cell while you type a formula to put its reference in; drag across cells for a range (B2:B9). The references in your formula are outlined in colour on the sheet, as a spreadsheet does.
- Some answers are filled down (or across) to other cells, as you would with the fill handle. Relative references (B2) move as the formula is copied; absolute references ($H$2) stay on the same cell. Fill-Down Lab shows how.
- A wrong answer says what's different, and names the mistake if it's a common one. Show the answer gives a formula that works (that question then doesn't count as right first time). At the end of each level, a review lists every question, your formula and the model answer.
The three levels. Basics: + − * / and SUM, AVERAGE, MIN, MAX, COUNT. Conditions: IF, nested IF, AND, OR, COUNTIF, SUMIF and AVERAGEIF. Lookups and text: VLOOKUP, XLOOKUP, INDEX and MATCH, LEFT, MID, RIGHT, LEN, CONCAT and ROUND.
Key ideas. A formula starts with =. Text in a formula goes in double quotes ("Yes"); a condition in COUNTIF goes in quotes too (">=16"). Nested IFs test the highest band first. VLOOKUP needs FALSE as its fourth argument for an exact match (codes, names); leave it out (or use TRUE) for bands, where the first column must be sorted smallest first.
Common exam mistakes: forgetting $ on a cell that every row needs (a tax rate, a total, a lookup table); leaving out FALSE in VLOOKUP; testing the lowest grade first in a nested IF; using > where the question says "or more" (>=); COUNT where COUNTIF is meant (COUNT only counts numbers); SUMIF without the range to add up; typing the answer instead of a formula; rounding with INT (which always rounds down).
Objective: Cambridge IGCSE ICT (0417) section 20, spreadsheets: formulae and functions, relative and absolute references, and named cells; Cambridge International AS Level IT (9626) spreadsheets: functions including conditional, lookup and text functions.
Where this fits
- Cambridge: Cambridge IGCSE Information and Communication Technology (0417); Cambridge A Level Information Technology (9626); Cambridge AS Level Information Technology (9626) Goes beyond Cambridge IGCSE Information and Communication Technology (0417): INDEX/MATCH, SUMIF/AVERAGEIF and the text functions (LEFT, MID, LEN, CONCAT) go beyond 0417's list; they're in 9626.
- Pearson Edexcel International: Edexcel International GCSE ICT (4IT1) Goes beyond Edexcel International GCSE ICT (4IT1): Nested IF, SUMIF/AVERAGEIF, XLOOKUP, INDEX/MATCH and the text functions go beyond 4IT1's function list.
