Formulas and Functions
Building formulas
Every formula starts with = and uses operators: + − * / and ^ for power, plus comparison operators (=, >, <). Brackets control order of operations (multiplication before addition). Cell references, constants and functions can be combined. A frequent trap is entering a formula without =, which stores it as text, or using a comma vs semicolon separator depending on regional settings.
Essential functions
Know the purpose and syntax of core functions: SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, ROUND, IF, and COUNTIF/SUMIF. SUM adds a range; AVERAGE divides the total by the count of numeric cells; COUNT counts numbers only, while COUNTA counts non-empty cells (including text). ROUND takes a value and the number of decimals. IF takes a logical test, a value if true, and a value if false.
Error values
Recognise common errors: #DIV/0! (division by zero or empty cell), #REF! (reference to a deleted cell), #NAME? (unrecognised function or name), #VALUE! (wrong data type), and ##### (column too narrow - a display issue, not an error). Questions often ask you to diagnose an error from its cause.
Absolute references in functions
When a formula with a range is copied down, lock the range with $ (for example =B2/$B$10) so the denominator stays fixed. This is one of the most tested traps.
Rule of thumb
Read a function as what it does then what it needs: SUM needs numbers to add; IF needs a test, a true result and a false result. Check brackets are balanced and separators match your locale.
Worked example
=IF(A2>=50,"Pass","Fail") returns Pass when A2 is 50 or more, otherwise Fail. Copying =B2/$B$10 down keeps the divisor fixed because of the $ signs.