The Formula Gym
XP 0
Streak 0

The AVERAGE function in Excel

AVERAGE returns the mean of a set of numbers: the total divided by how many numbers there are.

What AVERAGE does

It adds the numbers in a range and divides by the count of numbers. Empty cells and text are ignored, so they do not pull the average down. A zero is a number, though, so a zero is counted.

Syntax

=AVERAGE(number1, [number2], ...)
number1
The first number, cell or range to average.
number2, ...
Optional. More numbers, cells or ranges.

Worked example

AB
1StudentScore
2Ana72
3Ben88
4Cy64
5Di91
6Eli85
7Average80
B7 =AVERAGE(B2:B6)

The five scores add up to 400, and 400 divided by 5 is 80.

Now you try

A fresh exercise every time. Press Another exercise as often as you like.

Where you will use it

Finance

Find the average monthly spend per team.

HR

Work out the average score in a review round.

Sales & Ops

Track the average order value.

Common mistakes

  • Expecting blank cells to count as zero. They are skipped. If a blank should mean zero, type a 0.
  • Averaging averages. If groups have different sizes, the average of their averages is misleading. Average the raw numbers instead.
  • Numbers stored as text are ignored, which changes the result without any warning.

The AVERAGE course

  1. 1
    Find the averageFree practice below
    Practise
  2. 2
    Average that skips zerosMembers lesson
  3. 3
    Average by categoryMembers lesson
  4. 4
    Moving averages over timeMembers lesson
  5. 5
    Average of the big onesMembers lesson
  6. 6
    Count above the averageMembers lesson

Common questions

What is the difference between AVERAGE and MEDIAN?

AVERAGE is the mean. MEDIAN is the middle value, and it is less affected by a few extremely large or small numbers.

Does AVERAGE ignore zeros?

No. Zeros are counted as numbers. Only blank cells and text are ignored.

Related functions

Unlock every lesson

Every function has a free lesson. Members get all the lessons, fresh exercises every time and the weekly leaderboard. £7 a month or £49 a year, and you can cancel at any time.

See membership