AVERAGE

About this lesson

AVERAGE adds up the numbers in a range and divides by how many there are. It skips empty cells and text, but it counts every zero.

By the end you can

average a range with AVERAGE and say what it skips.
explain why one zero moves an average and an empty cell does not.
average cells that are not next to each other.

Practises AVERAGE SUM COUNT MEDIAN

The idea

The function is simple. What it skips is the whole lesson. An empty cell is left out of the adding and the counting. So a week with one closed day averages over six days, not seven. A zero is a number and stays in. So a day that opened and sold nothing pulls the average down, just like a day of real losses. Neither behaviour is wrong. What goes wrong is a sheet where somebody typed 0 to mean "no figure yet".

When not to use it: a set with one value far from the rest. Six houses at 200,000 and one at 1,500,000 average nearly 400,000, which describes none of them. MEDIAN, later in this module, is the tool for that.

The mistake to watch for

Typing 0 to mean "no figure yet". AVERAGE skips an empty cell but counts a zero. So one placeholder zero pulls a week's average down, like a real day of nothing. Leave the cell empty when there is nothing to average. When an average looks low, count the zeros in its range before you doubt the data.

Where this comes up again

The SUM Function Spreadsheet Basics
FILTER Dynamic Arrays
SUMPRODUCT: Weighted Totals and Conditions Summaries and Reports
COUNT and COUNTA Spreadsheet Basics

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In B10, work out the average day's takings with AVERAGE, over the whole week from Monday to Sunday.

To begin, type it exactly:

=AVERAGE(B2:B8)

B10

Row	A	B	C
1	Day	Takings
2	Monday	412
3	Tuesday	386
4	Wednesday		Closed
5	Thursday	455
6	Friday	610
7	Saturday	728
8	Sunday	0	Power cut
9
10	Average day
11	Check

Every step

1

In B10, work out the average day's takings with AVERAGE, over the whole week from Monday to Sunday. To begin, type it exactly: =AVERAGE(B2:B8).

Hint. One range, B2 to B8.
2

Wednesday's cell is empty because the café was shut. Sunday's holds a 0 because it opened and sold nothing. Read =COUNT(B2:B8) and say what it returns.

Hint. Is a 0 a number? Is an empty cell?
3

Prove what AVERAGE did. In B11, divide the week's total by the number of days that hold a figure. Use SUM and COUNT.

Hint. SUM of the range, divided by COUNT of the same range.
4

The power cut means Sunday's 0 is not really a trading day. If it were cleared out, the average would be over Monday to Saturday. Read =AVERAGE(B2:B7) and say what it returns.

Hint. Five days, five numbers.
5

In D2, write a formula with AVERAGE that gives the average of Friday and Saturday only.

Hint. Friday is row 6, Saturday is row 7.