FormulaGym
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 zerosComing soon
  3. 3
    Weighted averagesComing soon
  4. 4
    Moving averages over timeComing soon
  5. 5
    Average by categoryComing soon
  6. 6
    Spot outliers in an averageComing soon

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