The AVERAGE function in Excel
AVERAGE adds a set of numbers and divides by how many there are, giving you the arithmetic mean. Use it for typical order value, average response time or mean test scores. It quietly skips empty cells and text, which is usually what you want but occasionally a trap.
Syntax
=AVERAGE(number1, [number2], ...)
| number1 | The first number, cell or range to average. |
|---|---|
| number2, ... | Optional. More numbers or ranges to include in the same average. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
The store tracks visitors per day. In D2, use AVERAGE to find the average daily visitors from B2:B7.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Visitors | Average | |||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 | |||||||
| 9 | |||||||
| 10 |
Tip: Select the visitor counts from Monday through Saturday.
Examples
=AVERAGE(B2:B7)→ 4.38Average satisfaction score for week 1. Sofia's blank cell is skipped, so it divides by five agents, not six.
=AVERAGE(B2:C7)→ 4.427272727A two-column range averages every score from both weeks together.
=ROUND(AVERAGE(C2:C7),2)→ 4.47Wraps the average in ROUND so the result is stored to two decimals, ready for a report.
Example data
| 1 | Agent | Week 1 | Week 2 |
|---|---|---|---|
| 2 | Priya | 4.6 | 4.8 |
| 3 | Marcus | 4.1 | 4.3 |
| 4 | Elena | 4.9 | 4.7 |
| 5 | Sofia | 4.4 | |
| 6 | Dmitri | 3.8 | 4 |
| 7 | Kofi | 4.5 | 4.6 |
Common mistakes
Blanks and zeros are not the same
A blank cell is ignored, but a
0counts and pulls the average down. Leave cells empty for "no data" and only type 0 when the real value is zero.#DIV/0! error
The range has no numbers at all, so there's nothing to divide by. Check the range, or wrap the formula:
=IFERROR(AVERAGE(B2:B7),"No data").Numbers stored as text are skipped
Imported values that are really text won't be averaged, which can skew the result without any warning. Convert them to numbers first.
Averaging averages
The mean of two group averages is wrong when the groups are different sizes. Average the raw values instead, or weight each group by its count.
FAQ
Does AVERAGE ignore blank cells?+
Yes. Empty cells and text are left out of both the total and the count. Cells containing 0 are included.
How do I average only some rows?+
Use AVERAGEIF for one condition, like one region, or AVERAGEIFS for several conditions.
How do I round an average to 2 decimal places?+
Wrap it in ROUND: =ROUND(AVERAGE(B2:B7),2). Formatting the cell only changes how it looks, while ROUND changes the stored value.
Lesson
Master AVERAGE step by step: AVERAGE, MIN and MAX
Summarize a list with its average, smallest and largest values.