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

By Ali, Founder, Excel Lessons

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`, type:

=AVERAGE(B2:B8)

That averages the whole week.

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, type: =AVERAGE(B2:B8). That averages the whole week.

Hint. One range, B2 to B8.
2

COUNT counts numbers, including 0. It skips empty cells and text. Read =COUNT(B2:B8) and say what it returns.

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

In B11, divide the week's total by the day count. Shape: =SUM(range)/COUNT(range). That proves what AVERAGE just did.

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

Without Sunday, the average covers Monday to Saturday only. Read =AVERAGE(B2:B7) and say what it returns.

Hint. Five days, five numbers.
5

In D2, use AVERAGE for Friday and Saturday only.

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