=AVERAGE(B2:B7) returns the average of the numbers in B2:B7: it adds them up and divides by how many numbers there are. Type it in an empty cell, or pick Home > AutoSum > Average and Excel writes it for you.
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Ana | 78 |
| 3 | Ben | 92 |
| 4 | Chen | 65 |
| 5 | Dina | 88 |
| 6 | Eli | 71 |
| 7 | Fay | 84 |
| 8 | Average | 79.66666667 |
The six scores add up to 478, and 478 divided by 6 is about 79.67. Change Chen's score to 95 and the average rises to 84.67. To show fewer decimals, use Home > Decrease Decimal, or round the result itself with =ROUND(AVERAGE(B2:B7),1) (ROUND explains the difference).
AVERAGE syntax
=AVERAGE(number1, [number2], ...)
Like SUM, it takes ranges, cells and numbers, separated by commas: =AVERAGE(B2:B7,D2:D7) averages two columns together, and =AVERAGE(B2:D2) averages across a row. Text, TRUE/FALSE in a range and empty cells are skipped.
Blank cells vs zeros
This is where most wrong averages come from. AVERAGE skips an empty cell, but a 0 is a number and counts. The two columns below are the same except for Ben: in column B his cell is empty, in column C it holds 0.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Blank | Zero |
| 2 | Ana | 80 | 80 |
| 3 | Ben | 0 | |
| 4 | Chen | 70 | 70 |
| 5 | Dina | 90 | 90 |
| 6 | Average | 80 | 60 |
| 7 | Numbers counted | 3 | 4 |
Column B gives 80 (240 divided by 3) and column C gives 60 (240 divided by 4). Neither is wrong; decide whether a missing value means "not taken" or "scored zero". A formula that returns "" to look empty is skipped like a blank cell.
Average without zeros
When zeros mean "no data" and should not drag the result down, use AVERAGEIF with the condition "<>0" (not equal to zero):
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Day | Sales | Ignoring zeros | Plain average | |
| 2 | Mon | 420 | 440 | 293.3333333 | |
| 3 | Tue | 0 | |||
| 4 | Wed | 380 | |||
| 5 | Thu | 0 | |||
| 6 | Fri | 510 | |||
| 7 | Sat | 450 |
D2 gives 440, the average of the four days with sales. The plain average in E2 is about 293.33, because it divides by six days. "<>0" also leaves out empty cells, since AVERAGEIF never counts them. More conditions are on the AVERAGEIF page.
Average of the top 3 values
LARGE returns the k-th largest value. Give it the array {1,2,3} and it returns the three largest at once, and AVERAGE averages them:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Round | Points | Top 3 average | Bottom 3 average | |
| 2 | 1 | 64 | 85.33333333 | 64.66666667 | |
| 3 | 2 | 81 | |||
| 4 | 3 | 77 | |||
| 5 | 4 | 90 | |||
| 6 | 5 | 58 | |||
| 7 | 6 | 85 | |||
| 8 | 7 | 72 |
The top three are 90, 85 and 81, so D2 gives 85.33 (rounded). The bottom three are 58, 64 and 72, giving 64.67. In Excel 2021 and Microsoft 365, =AVERAGE(LARGE(B2:B8,SEQUENCE(5))) averages the top 5 without typing the list.
AVERAGEA: when text should count as zero
AVERAGEA counts text and FALSE as 0 and TRUE as 1. With the scores 80, 70 and the word "absent" in B2:B4:
=AVERAGE(B2:B4) returns 75 (80 + 70, divided by 2)
=AVERAGEA(B2:B4) returns 50 (80 + 70 + 0, divided by 3)
Use AVERAGEA only when a text entry really means zero. Empty cells are skipped by both.
Practice: average without the zeros
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Day | Visits | Average (no zeros) | ||
| 2 | Mon | 120 | |||
| 3 | Tue | 0 | |||
| 4 | Wed | 135 | |||
| 5 | Thu | 98 | |||
| 6 | Fri | 0 | |||
| 7 | Sat | 160 | |||
| 8 | Sun | 142 |
Your turn: In E2, average the visits in B2:B8, leaving out the days with 0 visits.
When the average misleads: use MEDIAN
One extreme value moves the average a lot. MEDIAN returns the middle value instead, which is what a "typical" value usually means.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Salary | Average | Median | |
| 2 | Ana | $42,000 | $78,800 | $47,000 | |
| 3 | Ben | $45,000 | |||
| 4 | Chen | $47,000 | |||
| 5 | Dina | $50,000 | |||
| 6 | Owner | $210,000 |
The average salary is 47,000. When a few values are far from the rest, report the median, or both. To give some values more weight than others, such as an exam that counts double, see weighted average.
Frequently Asked Questions
What is the formula for average in Excel?
=AVERAGE(B2:B7). It adds the numbers in the range and divides by how many numbers there are. Empty cells and text are left out of both the total and the count.
Does AVERAGE count blank cells in Excel?
No. An empty cell is skipped, so =AVERAGE(B2:B5) with one empty cell divides by 3. A cell holding 0 is counted, so it pulls the average down.
What is the difference between AVERAGE and MEDIAN in Excel?
=AVERAGE(B2:B6) adds the values and divides by how many there are; =MEDIAN(B2:B6) returns the middle value once they are sorted. One extreme value moves the average but not the median: for 42000, 45000, 47000, 50000 and 210000 the average is 78800 and the median 47000.
How do I average the top 3 values in Excel?
=AVERAGE(LARGE(B2:B8,{1,2,3})) averages the three largest values. For the bottom 3, use SMALL instead of LARGE. For the top 5 in Excel 2021 or Microsoft 365, =AVERAGE(LARGE(B2:B8,SEQUENCE(5))).
Why does AVERAGE return #DIV/0!?
The range has no numbers at all: it is empty, or every value is text, such as numbers stored as text. AVERAGE then divides by zero.
