Formulas and Functions

The main advantage of a spreadsheet is automatic calculation. To calculate, you write a formula in a cell. A formula always starts with the equals sign (=), for example =A1+B1; the cell shows the result of the formula, not the formula itself.

Formulas use numbers, arithmetic operators (+, -, *, /, ^) and the addresses of other cells. If the number in cell A1 or B1 changes, the result is recalculated automatically. When you copy a formula downward by dragging, the addresses change accordingly: the formula =B2*C2 becomes =B3*C3 one row lower.

A function is a ready-made calculation rule. =SUM(A1:A5) finds the sum of the numbers in cells A1 to A5, =AVERAGE(A1:A5) finds the average, =MAX(A1:A5) the largest number and =MIN(A1:A5) the smallest. After the function name, the arguments, which are cell or range addresses, are written in parentheses. In localized versions of the program, function names may be written in the local language.

Mistakes can happen when entering formulas. If an error code appears in a cell (for example, #DIV/0! when dividing by zero), check the formula again. Always compare the result with a simple manual calculation to make sure it is correct.

Key takeaways

A formula starts with the equals sign (=), and the cell shows its result.
If a formula uses cell addresses, the result updates automatically when the numbers change.
When a formula is copied, the cell addresses change accordingly.
SUM, AVERAGE, MAX and MIN are the most commonly used functions.
An error code means the formula needs to be checked again.

Practice tasks

Enter three numbers in cells A1:A3 and find their sum in cell A4 in two ways: with the formula =A1+A2+A3 and with the function =SUM(A1:A3).
Build a small “shop receipt” table: price in column B, quantity in column C and the amount in column D using the formula =B2*C2; drag the formula down to the rows below and find the total.
For a list of grades, find the average, the highest and the lowest score using the AVERAGE, MAX and MIN functions.

Review questions

Test yourself: think first, then reveal the answer.

1Which sign does a formula start with?Show answer

The equals sign (=).

2What does the function SUM(A1:A5) calculate?Show answer

The sum of the numbers in cells A1 to A5.

3Why is it better to use a cell address in a formula instead of a number?Show answer

If the number in the cell changes, the result is recalculated automatically.