Formulas and Functions
Scope
Formulas and Functions is the highest-weight area: operator precedence, reference types, function syntax and diagnosing error values.
Rules
Every formula starts with = and evaluates by precedence: negation, percent, exponent, multiply/divide, add/subtract, concatenation (&), then comparisons. Parentheses override this. References can be relative (A1), absolute ($A$1) or mixed (A$1, $A1), and F4 cycles through them while editing. A 3D reference such as SUM(Jan:Mar!B2) totals the same cell across a range of sheets.
Functions use =NAME(argument1, argument2). Common exam functions include SUM, AVERAGE, COUNT, COUNTA, COUNTBLANK, COUNTIF(S), SUMIF(S), IF, IFERROR, ROUND, VLOOKUP, INDEX/MATCH and TEXT.
Worked example
A price table sits in Products!A2:D100, with the product code in column A and price in column D. The formula must be copied down 50 rows. =VLOOKUP(A2,Products!A2:D100,4,FALSE) breaks on the second row because the table reference shifts. Locking it as =VLOOKUP(A2,Products!$A$2:$D$100,4,FALSE) keeps the lookup range fixed while A2 becomes A3, A4 and so on. The FALSE argument demands an exact match; omitting it permits an approximate match, which requires the first column sorted ascending.
Error values
- #DIV/0! - divisor is zero or empty.
- #N/A - lookup value not found; often wrapped in IFERROR.
- #REF! - a referenced cell was deleted.
- #NAME? - misspelled function or undefined name.
- #VALUE! - wrong argument type, such as text in arithmetic.
- - column too narrow or a negative date, not an error value.
Traps
- COUNT counts numeric cells only; COUNTA counts any non-empty cell.
- ROUND changes the stored value; number formatting changes only the display.
- A circular reference makes the formula return 0 and triggers a warning.
- Text that looks numeric, such as "10" with a leading space, is ignored by SUM.