ICDL Spreadsheets (ICDL): Formulas and Functions

What you need to understand

Every formula begins with =. Formulas contain values, cell references and operators, and follow normal order of operations (brackets, then exponent, then multiply/divide, then add/subtract). A function is a built-in formula such as =SUM(A1:A10), =AVERAGE(B2:B20), =MIN(...), =MAX(...), =COUNT(...), =COUNTA(...), =ROUND(x,2), =IF(test, value_if_true, value_if_false) and =TODAY().

You must understand relative versus absolute references. B2 is relative and shifts when copied; $B$2 is absolute and stays fixed; B$2 and $B2 lock only the row or only the column. A reference to another sheet is written Sheet2!A1.

Know the standard error values: #DIV/0! (dividing by zero or an empty cell), #REF! (a referenced cell was deleted), #NAME? (misspelled function name or unquoted text), #VALUE! (wrong type of argument).

Traps

• Omitting the = turns a formula into text.
• Forgetting $ when copying a formula that should always point at one fixed rate cell.
• COUNT counts numeric entries only, while COUNTA counts all non-empty cells - a very common exam distinction.
• AVERAGE ignores blank cells and text rather than treating them as zero.

Worked example: with a price in A2 and a fixed tax rate in $C$1, the formula =A2*$C$1 can be filled down the column and will always use C1.