AVERAGE in Excel
Last updated
AVERAGE adds up a group of numbers and divides the total by how many numbers there are. For example, =AVERAGE(B2:B13) returns the average of the 12 values in B2:B13. Empty cells and text are ignored, but zeros are counted.
AVERAGE syntax
=AVERAGE(number1, [number2], ...)
- number1
- The first number, cell or range to average, e.g.
B2:B13. This is the only required argument. - number2, ...
- Optional extra numbers, cells or ranges, separated by commas, up to 255 in total. Use them to average cells that aren’t side by side:
=AVERAGE(B2:B4,B6:B7).
Practice AVERAGE here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Average a column
In B8, calculate the average expense claim of B2:B7 with AVERAGE.
| Employee | Expenses ($) |
| Olivia Reed | 182.40 |
| James Carter | 95.00 |
| Sofia Patel | 240.60 |
| Liam Brooks | 128.00 |
| Emma Hughes | 310.50 |
| Noah Bennett | 77.90 |
| Average |
How to use AVERAGE, step by step
- Click the cell where you want the result and type
=AVERAGE(. - Select the range of numbers to average, e.g. B2:B13. Don’t include the header or the total row.
- To add another range or cell, type a comma and select it. Hold Ctrl to select several areas with the mouse.
- Type
)and press Enter. Excel shows the average of all the numbers you selected.
AVERAGE examples
Average of a column
=AVERAGE(B2:B13)
Returns the average of the 12 monthly values in B2:B13.
Average of cells that aren’t side by side
=AVERAGE(B2:B4,B6:B7)
Averages two ranges and skips B5, for example a store that was closed that month.
Average rounded to 1 decimal
=ROUND(AVERAGE(B2:B13),1)
ROUND changes the value itself, so later calculations use the rounded number.
Average without zeros
=AVERAGEIF(B2:B13,"<>0")
AVERAGEIF only averages the cells that meet a condition: here, every cell that isn’t 0.
Common AVERAGE errors and how to fix them
| Result | Cause | Fix |
|---|---|---|
| #DIV/0! | The range contains no numbers at all: every cell is empty or holds text. | Check the range, convert numbers stored as text, or wrap the formula: =IFERROR(AVERAGE(B2:B13),""). |
| #N/A, #VALUE!… | One cell in the range already shows an error. AVERAGE passes it on. | Fix the error in the source cell, or use AGGREGATE to ignore errors. |
| #NAME? | The function name is misspelled, e.g. =AVERGE(B2:B13). | Check the spelling, or pick AVERAGE from the list Excel shows as you type. |
| Wrong value, no error | Zeros are counted while blanks are ignored, or numbers stored as text are left out. | Leave out cells that shouldn’t count, use AVERAGEIF to skip zeros, and convert text to numbers. |
AVERAGE vs AVERAGEIF vs MEDIAN
AVERAGE gives the mean of every number in the range. AVERAGEIF averages only the cells that meet a condition, such as one region or values other than 0. MEDIAN returns the middle value instead, which is often more useful when one very large or very small number would pull the average away from a typical value.
Frequently asked questions
Does AVERAGE ignore blank cells?
Yes. AVERAGE skips empty cells and text, so they don’t change the result. A cell that contains 0 is a number, though, and it is counted.
How do I average without zeros?
Use AVERAGEIF with a “not equal to 0” condition: =AVERAGEIF(B2:B13,"<>0").
How do I round an average?
Put AVERAGE inside ROUND: =ROUND(AVERAGE(B2:B13),1) rounds to 1 decimal. The Decrease Decimal button only changes how the number looks, not its value.
Why does AVERAGE return #DIV/0!?
There are no numbers in the range, so Excel would have to divide by zero. This often happens with an empty month or with numbers stored as text.
Keep practising
All 5 AVERAGE exercises, with explanations and progress tracking.
Open the AVERAGE practice set