Average Excluding Zeros

Excel Formulas › Average

All versionsAVERAGEIF

Blank cells are skipped by AVERAGE, but explicit zeros drag the mean down. AVERAGEIF with a "<>0" criterion averages only the non-zero values.

Quick formula: average B2:B100, ignoring zeros:
=AVERAGEIF(B2:B100, "<>0")
The <>0 criterion keeps every value except exact zeros out of the mean.

Functions used (tap for the full reference guide):

AVERAGEIF

The example

Zeros excluded from the average.

AB
1ScoreAvg (no 0)
28085
30
490

The formula

The formula:

=AVERAGEIF(B2:B100, "<>0") // skips exact zeros

How it works

How it works:

  1. AVERAGEIF(range, "<>0") averages only cells that are not equal to zero.
  2. Plain AVERAGE ignores blanks already, but counts a typed 0 — which lowers the result.
  3. Use this when zeros mean “no data” rather than a real measurement.
  4. To also exclude blanks explicitly or add a second test, switch to AVERAGEIFS.

Zero vs blank matters. If a survey leaves un-answered cells empty, AVERAGE already skips them. But if missing answers were entered as 0, they count — and AVERAGEIF(range,"<>0") is the fix. Decide whether a zero is real data first.

Try it: interactive demo

Live demo

Values (commas).

With 0s · No 0s

Variations

Exclude zeros & blanks

AVERAGEIFS:

=AVERAGEIFS(B:B, B:B, "<>0", B:B, "<>")

Above a threshold

Only big values:

=AVERAGEIF(B:B, ">=50")

Ignore negatives too

Positives only:

=AVERAGEIF(B:B, ">0")

Pitfalls & errors

Blanks already skip. AVERAGE never counts empty cells — only typed zeros are the problem.

All zeros → #DIV/0!. If nothing passes the criterion, there’s nothing to average.

Quote the operator. The criterion must be the text "<>0", in quotes.

Practice workbook

📊
Download the free Average Excluding Zeros practice workbook
An average-excluding-zeros sheet with the AVERAGEIFS, threshold, and positives variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I average a range but ignore zeros in Excel?
Use =AVERAGEIF(range, "<>0"). The <>0 criterion averages every value except exact zeros. Blanks are already ignored by AVERAGE.
Does AVERAGE count blank cells?
No — AVERAGE skips truly empty cells. It only counts cells containing 0, which is why AVERAGEIF with <>0 is needed.
What if all values are zero?
AVERAGEIF returns #DIV/0! because no cells meet the criterion. Wrap it in IFERROR if that's possible.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Average by group · Average ignore errors · Weighted average

Function references: AVERAGEIF