Formulas and Functions: References, Operators and Error Values
Writing formulas
Every formula begins with = and combines values, cell references, operators and functions. Operator precedence runs brackets, percent, exponent, multiplication and division, addition and subtraction, then comparison; when in doubt, add parentheses. Relative references (A1) shift when copied, absolute references ($A$1) do not, and mixed references ($A1, A$1) lock only one part.
Functions to know
SUM, AVERAGE, MIN, MAX, COUNT (numbers only), COUNTA (non-empty cells), COUNTIF and SUMIF with criteria, IF with a logical test and two outcomes, ROUND(value, digits), and TODAY(). AutoSum inserts a SUM for the contiguous range above or to the left, so always check the range it guessed.
Error values
#DIV/0! means a zero or empty divisor; #NAME? means a misspelt function or unquoted text; #REF! means a deleted referenced cell; #VALUE! means the wrong data type; ##### usually means the column is too narrow, not a faulty formula.
Traps
- Text inside a range is ignored by AVERAGE and COUNT, which can make results look wrong.
- Criteria such as ">=60" need quotation marks; a cell reference used as a criterion does not.
- Some regional settings use semicolons instead of commas as separators.
- A circular reference, where a formula refers to its own cell, must be corrected.
Worked example
=IF(AVERAGE(B2:B6)>=60,"Pass","Fail") averages five marks, tests the result and returns one of two labels; =ROUND(SUM(C2:C10)*1.2,2) adds 20% and rounds to two decimals.