AVERAGE in Excel: how to use it
AVERAGE gives you the average of whatever you select. An empty cell isn't counted, but a 0 is.
Try the first exercises for freeIn the lesson MIN, MAX and AVERAGE
What does AVERAGE do?
AVERAGE adds up the numbers in a range and divides the result by how many numbers there are. That gives you the ordinary average, just as you'd work it out on a calculator.
Empty cells and text don't count, not even when dividing. A 0 does count, so it pulls the average down.
When do you use AVERAGE?
- The average revenue per salesperson: =AVERAGE(C2:C6).
- The average mark of a pupil or a student: =AVERAGE(B2:F2).
- The average number of visitors per day in a month: =AVERAGE(B2:B31).
- An average with a condition, such as only the rows from Leeds: for that, use AVERAGEIF.
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =AVERAGE(C2:C6)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
AVERAGE | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | AVERAGE |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
number1 | A cell, a range or a number. | C2:C6 |
[number2] | Optional: more cells, ranges or numbers, separated by commas. | left out |
, | A comma separates the arguments from each other. | , |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. | |
... | You can give more, with a comma between each one. |
AVERAGE step by step
E1 contains =AVERAGE(C2:C6). 3005 divided by 5 rows. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click the cell where the average should go, here E1, and type the name of the function with a bracket.
=AVERAGE( - Select the revenue figures, from C2 to C6.
=AVERAGE(C2:C6 - Close the bracket and press Enter: 3005 divided by 5 is 601.
=AVERAGE(C2:C6)
More examples
An empty cell doesn't count
D1 contains =AVERAGE(B2:B5). Tuesday is empty and doesn't count: 360 divided by 3 days. Type a 0 in B3, and the average drops to 90.
Rounded to one decimal place
F2 contains =ROUND(AVERAGE(B2:D2),1). The average is 14.333... With ROUND around it, you keep one decimal place.
Separate cells
E1 contains =AVERAGE(C2,C5). Only the two laptops: with a comma you choose separate cells.
Watch out for outliers
One extremely high value pulls the average up a lot. For salaries or prices, look at the median as well.
Where it often goes wrong
- An empty cell and a 0 give a different result.
- If there are no numbers at all, you get #DIV/0!.
Practise this in the lesson MIN, MAX and AVERAGE. The first exercises are free.
Frequently asked questions
How do I work out an average with a condition?
With AVERAGEIF, for example =AVERAGEIF(A2:A6,"Leeds",C2:C6).