Excel AVERAGE function: find the mean of a range
AVERAGE adds a range and divides by how many numbers it holds. =AVERAGE(B2:B7) returns the mean of the six sales. Blank cells are skipped, but cells with 0 are counted.
The examples use this small sales table, the same one in the practice box below.
| A Rep | B Sales | D Target | E Region | |
|---|---|---|---|---|
| 2 | Dana | 1,250 | 1,000 | East |
| 3 | Luis | 830 | 1,000 | West |
| 4 | Priya | 2,100 | 1,000 | East |
| 5 | Marcus | 475 | 1,000 | North |
| 6 | Jen | 990 | 1,000 | West |
| 7 | Omar | 1,600 | 1,000 | East |
Syntax
=AVERAGE(number1, [number2], ...)Example
=AVERAGE(B2:B7) returns 1,207.5. That's 7,245 divided by 6.
Blanks vs zeros
This is the most tested detail. If one rep's sale is blank, AVERAGE divides by 5. If it's 0, AVERAGE divides by 6 and the result drops. Make sure empty means "no data" and 0 means "sold nothing."
Rounding the result
Changing the number format only changes what you see. To change the value, wrap it in ROUND: =ROUND(AVERAGE(B2:B7),0) returns 1,208. Use 2 instead of 0 for cents.
Average only some rows
=AVERAGEIF(E2:E7,"East",B2:B7) averages only the East sales and returns 1,650. The argument order matches SUMIF.
Common mistakes
- Writing
=SUM(B2:B7)/7and counting the rows wrong. Let AVERAGE count. - Including the total row in the range.
- Formatting to hide decimals when the task asked for a rounded value.
Try it: Average sale
Show the average sale across the team in B8.
Graded live, like a real Excel test. Press New numbers to prove you didn't memorize the answer. More practice
Questions people ask
Does AVERAGE include zeros?
Yes. Zeros count as values. Blank cells and text are skipped.
How do I average without zeros?
Use =AVERAGEIF(B2:B7,"<>0") to skip cells equal to zero.
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
How to use SUM in Excel with a real example, the mistakes that cost points on Excel tests, and a live exercise to practice.
How to use COUNTIF in Excel with numbers, text and cell references, plus COUNTIFS for two conditions and a live exercise.
How to use SUMIF in Excel, the argument order that trips people up, using cell references in the condition, and a live exercise.