Detailed · Deep study
1Building a model
Insert and delete cells, rows and columns, and merge cells for titles. Create formulae using cell references (so results update when data changes) with the arithmetic operators + − * / ^ (indices).
| Formula | Function | |
|---|---|---|
| meaning | a calculation written by the user, using cell references and operators | a built-in, named formula that carries out a set calculation |
| example | =B2*C2+D2 | =SUM(B2:B10) |
Order of operations (BIDMAS): Brackets, Indices, Division and Multiplication, Addition and Subtraction. E.g. =2+34 gives 14, but =(2+3)4 gives 20. Use brackets to make sure formulae work as intended.
2Cell referencing and names
| Reference | Characteristics and use |
|---|---|
| relative (B2) | changes when the formula is replicated (copied) to other cells – used for most formulae, e.g. a total in each row |
| absolute ($B$6) | stays the same when replicated – used for a single fixed value such as a VAT rate or exchange rate |
| named cell / range | a cell or range given a name (e.g. VAT, Prices) that can be used in formulae; it acts as an absolute reference and makes formulae easier to read: =D2*(1+VAT) |
3Functions
| Function | Example | Result |
|---|---|---|
| SUM | =SUM(D2:D10) | adds the values |
| AVERAGE | =AVERAGE(C2:C10) | mean of the values |
| MAX / MIN | =MAX(C2:C10) | largest / smallest value |
| INT | =INT(7.8) | 7 (removes the decimal part) |
| ROUND | =ROUND(3.14159, 2) | 3.14 |
| COUNT | =COUNT(B2:B20) | number of cells containing numbers |
| COUNTA | =COUNTA(A2:A20) | number of non-empty cells |
| COUNTIF | =COUNTIF(C2:C20,">=50") | number of cells meeting a condition |
| IF | =IF(C2>=50,"Pass","Fail") | one value if the condition is true, another if false |
| LOOKUP | =LOOKUP(F2,A2:A10,B2:B10) | finds F2 in A2:A10 and returns the matching value in B2:B10 |
| VLOOKUP | =VLOOKUP(F2,$A$2:$C$10,3,FALSE) | looks down the first column for F2 and returns the value from column 3 (FALSE = exact match) |
| HLOOKUP | =HLOOKUP(F2,$A$1:$J$3,2,FALSE) | looks across the first row and returns from row 2 |
| XLOOKUP | =XLOOKUP(F2,A2:A10,C2:C10,"Not found") | searches one range and returns from another; can give a value if not found |
=IF(C2>=70,"Distinction",IF(C2>=50,"Pass","Fail"))
=ROUND(AVERAGE(C2:C20),1)
=VLOOKUP(A2,[Prices.xlsx]Sheet1!$A$2:$B$50,2,FALSE)4Exam focus
Key words: formula; function; operator; BIDMAS; relative reference; absolute reference; named range; VLOOKUP; IF; nested function.
Quick self-check
Answer in your head first, then reveal.
A formula is a calculation written by the user (1); a function is a built-in named formula, e.g. SUM (1).
The VAT cell must not change when the formula is copied (1) because every row uses the same rate (1).
19 (1).
