Maths and conditional totals

Excel AVERAGE Function: Work Out the Mean of a Range

Adds the numbers in a range and divides by how many there were.

AVERAGE returns the arithmetic mean: the total divided by the count. The subtlety is entirely in what gets counted. Blank cells are excluded from both the total and the count, so they do not drag the answer down. Zeros are included in both, so they do.

That distinction decides whether your average is right. Ten salespeople where two made no sales: if those two rows are blank, you get the average of eight; if they hold 0, you get the average of ten. Both are defensible numbers and only one answers the question you asked.

The other thing worth knowing is when not to use it. An average is easily dragged by one extreme value — a single enormous order pulls the mean above almost every actual order. When the distribution is skewed, MEDIAN describes it better, and quoting both is usually the honest answer.

Syntax

=AVERAGE(number1, [number2], …)

Arguments

number1 Required: The first range or value. Text and blanks in it are ignored.
number2, … Optional: Further ranges or values, up to 255.

The example data

Headers in row 1, data in A2:D6. Note the blank in C4 and the text in C6.

	A	B	C	D
1	Item	Units	Unit price	Region
2	Cordless drill	12	89.99	North
3	Extension lead	40	12.5	South
4	Safety goggles	8		North
5	Work gloves	25	5.4	South
6	Tool belt	6	n/a	North

Worked examples

=AVERAGE(B2:B6)

Result: 18.2

91 units across five rows.

=AVERAGE(C2:C6)

Result: 35.963…

Only three cells count: the blank C4 and the text "n/a" in C6 are excluded from the divisor as well as the total.

=AVERAGEIF(D2:D6, "North", B2:B6)

Result: 8.667…

The conditional version, following SUMIF's argument order: test range, criteria, average range.

=IFERROR(AVERAGE(C2:C6), "No data")

Result: 35.963…

Guarding against a range with no numbers at all, which would otherwise return #DIV/0!.

Now practise it

Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.

Open the exercise: AVERAGE Function

Common errors and how to fix them

#DIV/0!

Why it happens: There is nothing numeric in the range, so the count is zero and the division is impossible.

How to fix it: Wrap it: =IFERROR(AVERAGE(range), "No data"). This appears most often in templates before any data has been entered.

Average is higher than expected

Why it happens: Empty cells were excluded from the count when they should have counted as zero.

How to fix it: Decide which you mean. To count blanks as zero, use =SUM(range)/COUNTA(range) or fill the blanks with 0.

AVERAGEA gives a different answer

Why it happens: AVERAGEA counts text as 0 and includes it in the divisor, where AVERAGE ignores it entirely.

How to fix it: Use AVERAGE unless you specifically want text treated as zero. AVERAGEA surprises people far more often than it helps.

Tips worth knowing

AVERAGEIF and AVERAGEIFS mirror SUMIF and SUMIFS exactly, including the reversed argument order between them.
Quote MEDIAN alongside AVERAGE whenever the data has outliers — the gap between them is itself informative.
TRIMMEAN discards a percentage from each end before averaging, which handles a couple of extreme values.
To average only non-zero values: =AVERAGEIF(range, "<>0").

Frequently asked questions

Does AVERAGE include empty cells?

No. Blank cells are left out of both the total and the count, so they do not affect the result. Cells containing 0 are included in both and do lower the average — which is why replacing blanks with zeros changes the answer.

What is the difference between AVERAGE and MEDIAN?

AVERAGE is the total divided by the count and moves with every value, including extremes. MEDIAN is the middle value when sorted and barely moves at all. For salaries, order sizes, or anything with a long tail, the median usually describes the typical case better.

How do I average only rows meeting a condition?

Use AVERAGEIF: =AVERAGEIF(D2:D6, "North", B2:B6) averages the units for North rows only. For more than one condition, AVERAGEIFS takes the average range first, like SUMIFS.

Related functions

Guides that use it

Longer reads where this function does real work in a real sheet.

All functions