Formulas and Functions

Introduction

Adding cells one by one with + is slow. Functions are built-in calculations that work on whole ranges. They are the building blocks of almost every spreadsheet.

The data we will use

Throughout this course we use a small sales table, with a Revenue column calculated as Units × Price in column F:

	A	B	C	D	E	F
1	Month	Region	Product	Units	Price	Revenue
2	Jan	N	Pen	10	2.00	20
3	Jan	S	Pen	5	2.00	10
4	Feb	N	Book	3	12.50	37.5
5	Feb	S	Book	4	12.50	50
6	Mar	N	Pen	8	2.00	16
7	Mar	S	Book	2	12.50	25
8	Mar	N	Bag	1	30.00	30
9	Apr	S	Bag	2	30.00	60

The most useful functions

A function has a name, then its inputs in brackets.

=SUM(F2:F9)       total of the Revenue column  → 248.5
=AVERAGE(D2:D9)   average units per row        → 4.375
=MAX(F2:F9)       the largest revenue          → 60
=MIN(F2:F9)       the smallest revenue         → 10
=COUNT(D2:D9)     how many numbers             → 8

The results are worked out from the table above. Type the same data into your spreadsheet and check you get the same answers. Function names are not case sensitive, but the brackets and commas matter. Some language settings use semicolons instead of commas between inputs.

Quick quiz

Which formula adds up all the revenue values in F2 to F9?

Relative and absolute references

When you copy a formula, references normally shift. This is a relative reference. Copy =D2*E2 down one row and it becomes =D3*E3.

Sometimes a reference must stay fixed. Suppose cell I1 holds a tax rate of 20 percent, and you want each row's tax:

=F2*$I$1

The dollar signs lock the reference, making it absolute. Copy it down and it stays $I$1, while F2 changes to F3, F4 and so on. You can lock only the column ($I1) or only the row (I$1). Press F4 (in most programs) to cycle through the choices while editing.

The order of calculation

Spreadsheets follow normal maths order: brackets first, then powers, then multiplication and division, then addition and subtraction. Use brackets to make your intention clear.

=10+2*3     → 16
=(10+2)*3   → 36

Common errors

Message	Usual cause
`#DIV/0!`	Dividing by zero or an empty cell
`#NAME?`	A misspelt function or name
`#VALUE!`	Text where a number is needed
`#REF!`	A reference that no longer exists, such as a deleted column
`#N/A`	A lookup that found nothing (next course)

Click the cell and read the formula. Most errors are typing mistakes or text stored as numbers.

Good habits

Do not type numbers into formulas (hard-coding). Put them in cells, such as a tax rate, so you can change them in one place.
Check totals another way, such as a quick estimate.
Use clear headings so formulas are easier to understand.
Test with simple numbers first.

Try it yourself

Enter the sales table. Write formulas for the total revenue, the average units and the highest revenue. Put a tax rate of 20 percent in one cell, and add a column that calculates tax on each row with an absolute reference.

Summary

Functions such as SUM, AVERAGE, MIN, MAX and COUNT calculate over ranges.
Relative references shift when copied, and absolute references with $ stay fixed.
Learn the common errors, avoid hard-coded numbers in formulas and check totals.