Formula vs function
A formula starts with = and is a user-written expression of cell references, values and operators (+ - * / and ^ for power). It MAY contain functions, e.g. =SUM(B2:B5)/2. A function is a named, built-in operation (SUM, IF, VLOOKUP) — part of a formula, not a formula itself. Order of operations is BIDMAS: brackets, indices, division/multiplication, then addition/subtraction. So =B2+B3*B4 multiplies first; =(B2+B3)*B4 forces the add.
Relative vs absolute referencing
A relative reference (B2) shifts by the same rows/columns when a formula is replicated. An absolute reference locks a cell with \$: \$A\$1 locks both, \$A1 the column, A\$1 the row. Use absolute when a replicated formula must always point at ONE fixed cell, e.g. =B2*\$D\$1. Inserting/deleting rows or columns re-numbers references; a deleted cell a formula needs shows #REF!. Merged cells keep only the top-left content — avoid over numeric data.
The function library, named ranges and nesting
Core functions: SUM, AVERAGE, MAXIMUM/MINIMUM, INTEGER (truncates, no rounding), ROUND, COUNT/COUNTIF, IF, and the LOOKUP family — VLOOKUP down a column, HLOOKUP across a row, XLOOKUP any direction. A named cell/range labels a cell so \$D\$1 reads as TaxRate. Nested functions put one inside another, innermost first. External data (CSV, workbook, database) can be imported rather than re-typed (check its format afterwards).
Drawn from real examiner reports.
Formula and function treated as the same
Asked to "state the difference between a formula and a function", candidates describe just ONE term (usually the function) without contrasting it, or name a specific function ("SUM adds numbers") for the general definition. Both lose marks: the mark scheme rewards a two-element CONTRAST. Every function sits inside a formula, but =B2+B3 is a formula with no function.
s22 P12 Q9 — state the difference between a formula and a function; explain a VLOOKUP as a whole, not argument-by-argument.
Manual arithmetic where a function is asked
When a task says "use a function", an actual function call is required, not a formula that just gives the right number. =SUM(C4:F4) is accepted; =C4+D4+E4+F4 and =SUM(C4+D4+E4+F4) are both wrong (the second adds the values first, so SUM never sums a RANGE). The same applies to AVERAGE, MAX, MIN and COUNT: apply them to a range, not a pre-calculated value.
w23 P03 Q11/Q12 — =SUM(C4:F4) accepted; =C4+D4+E4+F4 and =SUM(C4+D4+E4+F4) not (a function was not used within the calculation).
Subtraction or division the wrong way round
=G4-L4 and =L4-G4 give different results (often opposite signs), so build the formula in the order the task states: "income minus expenditure" is =Income-Expenditure, never the reverse. Division is the same: =B2/B3 and =B3/B2 differ. Decide the order BEFORE typing — a reversed operator order scores nothing even when every reference is correct.
w23 P03 Q13-Q15 — the formula had to be built in the correct order (=G4-L4, not =L4-G4).
Replicating before fixing the reference
A reference that must stay on one fixed cell has to be made absolute (\$D\$1) BEFORE replicating. Left relative, it shifts down one row each time it is copied, so later rows point at empty or wrong cells — the formula looks right in row 1 and only breaks once replicated. The opposite error is making BOTH references absolute, so each row can no longer update to its own data.
w23 P03 Q13-Q15 — the correct reference had to be made absolute before the formula was replicated down to the correct row.
INTEGER truncates, ROUND rounds
INTEGER (INT) cuts off the decimal part with no rounding: INT(4.9) = 4. ROUND applies normal rounding to a stated number of decimal places: ROUND(4.9,0) = 5. They are not interchangeable — using one where the other is wanted gives a wrong whole-number result. INTEGER always takes a positive number DOWN, because it discards rather than rounds the fraction.
VLOOKUP vs HLOOKUP: column or row?
Choose the lookup by the direction the SEARCH values run, not the table's overall shape. VLOOKUP searches DOWN the first column; HLOOKUP searches ACROSS the first row. Codes down a column need VLOOKUP even in a many-column table; picking HLOOKUP "because there are several columns" fails. The column-index counts from the first column, so column D in A2:D50 is index 4.
Spot the function the wording asks for
Read the wording for the function it implies: "total" → SUM, "mean/average" → AVERAGE, "largest" → MAXIMUM, "smallest" → MINIMUM, "how many" → COUNT/COUNTIF, "look up" → VLOOKUP/HLOOKUP/XLOOKUP, "if/then" → IF. Then apply it to a cell RANGE, never as typed-out manual arithmetic.
Fix the reference before replicating
Before typing a formula with a fixed cell, ask "will this be replicated?" If yes, add \$ to the reference(s) that must stay locked BEFORE copying, not after. Build the operation in the stated order, and add brackets wherever default BIDMAS would calculate the wrong part first.
Evaluate nested functions inside-out
Work a nested formula from the INNERMOST function outward: find what the inner function returns first, then feed that into the outer function. In =IF(AVERAGE(C2:C6)>=50,"Pass","Fail"), compute AVERAGE(C2:C6) first, then let IF test that number against >=50.
Show the formula view as evidence
For a printed or screenshotted answer, show the formula view — not just the calculated values — so every function and reference you used is visible. A correct number with no visible formula is not enough evidence in a practical paper: the marks are for the formula.
=, built from cell references, values and arithmetic operators; it MAY contain one or more functions.+ add, - subtract, * multiply, / divide, ^ indices/power.B2) that automatically shifts when the formula is replicated.Full notes, flashcards, Q&A and the topic quiz for every premium subject.
Premium plans are US$8.99/month or US$49.99/year — first month free.
Studying with a parent's blessing? Show them this.