Excel AVERAGE Function — Why Blank and Zero Give You Two Different Answers

TL;DR — AVERAGE is sum ÷ count, but only over the numbers it finds: =AVERAGE(B2:B20). It skips blank cells and text entirely — they don't count toward the total or the divisor — but it counts a real 0 as the number it is, which drags the average down. That single distinction is the whole trap: recording "no sale" as 0 versus leaving the cell empty produces two different averages of the same reality, and the formula gives you no hint which one you got. AVERAGEA is the aggressive cousin that counts text and FALSE as 0 — almost never what you want. Reach for AVERAGE for a plain mean; decide at the data-entry stage what a missing value means.

=AVERAGE(B2:B20)          ' mean of the numbers only; blanks and text ignored
=AVERAGE(B2:B20, D2:D20)  ' multiple ranges are fine — pooled, then averaged
=AVERAGE(3, 5, 7)         ' constants work too -> 5
=AVERAGEA(B2:B20)         ' text and FALSE count as 0, TRUE as 1 — usually wrong

Search "AVERAGE function Excel" and you'll get =AVERAGE(number1, number2, …) and a note that it "returns the arithmetic mean," as if the only hard part were finding the button. But you already know what an average is. The thing that actually bites — the reason two analysts pointing AVERAGE at the same column get different numbers — isn't the arithmetic. It's what counts as a value. Blanks, zeros, and text each get treated differently, and those rules quietly decide your answer. That's what this page leads with.

What you'll learn

The mental model: AVERAGE is sum ÷ count, but only over actual numbers
The blank-vs-zero trap: why an empty cell and a 0 give different means
Why AVERAGE safely skips header text in a whole-column reference
What AVERAGEA really does — and why it's almost never the right choice
When an average is the wrong question: averages of averages, and percentages
The empty-set case: #DIV/0! and how to guard it

The mental model: sum ÷ count of the numbers

AVERAGE does exactly two things: it adds up every number in what you give it, and it divides by how many numbers there were. The word "numbers" is load-bearing. A blank cell is not a number, so it contributes to neither the sum nor the count — it's as if it isn't there. Text like "N/A" is not a number either, so it's skipped the same way. What survives to be averaged is only the genuine numeric cells.

This is usually what you want, and it's why =AVERAGE(B:B) down a whole column doesn't choke on the header label in B1 — the text is simply ignored. But the same leniency hides the trap in the next section, because there is one kind of "empty" that AVERAGE does not skip: a cell that actually contains 0.

The blank-vs-zero trap: the one that decides your answer

Here is the failure mode worth knowing. Say a salesperson made no sales this month. You have two ways to record that, and they are not interchangeable:

' B column: 10, 20, 0        (the "no sale" logged as a zero)
=AVERAGE(B2:B4)   ' -> 10     (0 counts: 30 ÷ 3)

' B column: 10, 20, (blank)  (the "no sale" left empty)
=AVERAGE(B2:B4)   ' -> 15     (blank skipped: 30 ÷ 2)

Same three people, same "no sale," and the average swings from 10 to 15 — a 50% difference — based purely on a data-entry convention nobody thinks of as a decision. A real 0 is a real number: it joins the sum (adding nothing) and, crucially, joins the count (adding one to the divisor), pulling the mean down. A blank joins neither.

Neither answer is universally "correct" — that's the point. If a zero-sales month genuinely counts as a zero-performance month, 10 is right. If those people were on leave and shouldn't be in the denominator at all, 15 is right. The danger is that the formula looks identical in both cases and silently commits you to whichever convention your data happens to use. Decide it on purpose: to exclude zeros from a mean, either leave the cell blank at entry, or filter them out explicitly with AVERAGEIF: =AVERAGEIF(B2:B4, "<>0").

AVERAGEA: the cousin that counts text as zero

AVERAGEA looks like a more thorough AVERAGE — "average, but include everything." What "include everything" actually means is that it coerces non-numbers instead of skipping them: text becomes 0, FALSE becomes 0, TRUE becomes 1, and blanks are still skipped.

' A column: 90, 80, "absent"
=AVERAGE(A2:A4)    ' -> 85    ("absent" skipped: 170 ÷ 2)
=AVERAGEA(A2:A4)   ' -> 56.7  ("absent" counted as 0: 170 ÷ 3)

That 56.7 is almost certainly not the number anyone wanted. A column of scores with a stray text label gets diluted — the label lands as a zero and drags the mean down hard. The honest use for AVERAGEA is narrow: when text in your data genuinely should be treated as zero (say, "pending" really does mean a zero-value deal for the purpose you're measuring). That case is rare. The default assumption should be AVERAGE; only reach for AVERAGEA when you can say out loud why a word deserves to count as 0.

When an average is the wrong question

The sharpest failures aren't about blanks or text — they're about averaging the wrong thing. Two patterns cause almost all of them:

Averaging averages. If each region already reports an average deal size and you AVERAGE those regional averages, you've given a 3-deal region and a 300-deal region equal weight. The result is a real number that answers no real question. When each figure summarizes a different-sized group, you need a weighted average, not a plain one — the total, divided by the total count. This is common enough to have its own guide: see Weighted Average in Excel.

Averaging rates and growth. Averaging percentages the plain way is a similar trap. A +50% year followed by a −50% year does not average to 0% — you end up down 25% — so AVERAGE of the two percentages lies to you. Compound growth needs the geometric mean, covered in GEOMEAN, TRIMMEAN & HARMEAN. The rule of thumb: AVERAGE is for independent quantities of the same kind and weight; the moment your numbers are summaries, percentages, or rates, ask whether a plain mean is even the right tool.

The empty-set case: #DIV/0!

If there are no numbers at all in the range — every cell blank or text — AVERAGE has a count of zero and can't divide by it, so it returns #DIV/0!. This is the same "mean of nothing is undefined" behavior that AVERAGEIFS shows when no rows match. When a range might legitimately be empty (a category with no entries yet), wrap it:

=IFERROR(AVERAGE(B2:B20), "—")   ' show a dash instead of #DIV/0! when empty

Make that a reflex for any average feeding a dashboard or a cell others build on — a single #DIV/0! propagates to everything downstream.

How ExcelMaster helps

The dangerous part of an average is never the typing — it's knowing that a logged 0 and a blank give different answers, that AVERAGEA quietly turns a text label into a zero, and that "average of the regional averages" is usually the wrong question. Ask ExcelMaster "why is my average lower than I expected?" and it checks the range for zeros that should be blanks and text that AVERAGEA is counting, then tells you which convention your data is actually using. Say "average deal size by region, weighted by number of deals" and it writes the weighted formula instead of the misleading plain mean.

Frequently asked questions

Does AVERAGE ignore blank cells in Excel?

Yes. AVERAGE skips truly empty cells entirely — they count toward neither the sum nor the divisor. But a cell containing 0 is a real number and is included, which lowers the average. That difference between blank and zero is the most common reason an average isn't what you expected.

What is the difference between AVERAGE and AVERAGEA?

AVERAGE averages only numbers, skipping text and logical values. AVERAGEA includes them by coercion: text and FALSE count as 0, TRUE counts as 1. On a column of scores with a stray text label, AVERAGE ignores the label while AVERAGEA counts it as zero and drags the mean down — so AVERAGE is almost always the one you want.

Why is my AVERAGE returning #DIV/0!?

Because the range contains no numbers to average — every cell is blank or text, so the count is zero and Excel can't divide by it. Wrap it to handle the empty case: =IFERROR(AVERAGE(B2:B20), "—"). If you expected numbers there, check that they aren't stored as text (left-aligned, with a green triangle).

How do I average cells but ignore zeros?

Use AVERAGEIF with a "not equal to zero" criterion: =AVERAGEIF(B2:B20, "<>0"). This excludes real zeros from both the sum and the count, giving the mean of only the non-zero entries — useful when a 0 means "no data" rather than a genuine measured value.

How do I average a filtered or visible-only list?

AVERAGE includes hidden rows. To average only the rows left visible by a filter, use SUBTOTAL: =SUBTOTAL(101, B2:B20) averages just the visible cells and updates as you filter.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-27.