AI Excel Generator
← ALL GUIDES

How to Calculate an Average in Excel Excluding Zeros

Updated November 4, 2026 · AI Excel Generator

To calculate an average in Excel while ignoring zeros, use AVERAGEIF with a criterion that excludes zero.

The result can differ from a regular average more than you might expect, especially when your range contains blanks, errors, or formulas returning zero.

Use AVERAGEIF to exclude zeros

For values in cells A2:A10, enter:

=AVERAGEIF(A2:A10,"<>0")

The criterion "<>0" means “not equal to zero.” Excel includes numeric values other than zero in the average and excludes zero values.

Blank cells in the range are also ignored.

For example, if A2:A6 contains 10, 0, 20, 0, and 30, the formula averages 10, 20, and 30.

The result is 20.

A regular =AVERAGE(A2:A6) includes the zeros and returns 12.

Average values that meet additional conditions

When your data has categories, use AVERAGEIFS to exclude zeros while averaging only rows that meet another condition.

Suppose column A contains departments and column B contains scores.

To average nonzero scores for Sales, use:

=AVERAGEIFS(B2:B20,A2:A20,"Sales",B2:B20,"<>0")

The first range, B2:B20, is the values to average.

The following pairs specify a criteria range and criterion: department must be Sales, and score must not equal zero.

All ranges should have matching dimensions.

For older Excel versions without AVERAGEIFS, use AVERAGEIF when there is only one criterion.

For multiple conditions, an array formula or a helper column may be needed, depending on the version.

Use AVERAGE with FILTER in Microsoft 365

In Microsoft 365 and Excel 2021 or later, FILTER can make the filtering step visible within the formula:

=AVERAGE(FILTER(A2:A10,A2:A10<>0))

FILTER returns the values in A2:A10 that are not zero, and AVERAGE averages that filtered result.

If every cell is zero or no qualifying value exists, FILTER returns a #CALC! error by default.

You can provide an optional third argument to return a message or other value when no matches exist:

=IFERROR(AVERAGE(FILTER(A2:A10,A2:A10<>0)),"No nonzero values")

This version returns the text message when the calculation has no valid results.

Use it only when returning text instead of a numeric answer is appropriate for your report.

Choose the right formula for your Excel version

  • Excel 2007 and later: AVERAGEIF is available for a single condition.
  • Excel 2007 and later: AVERAGEIFS is available for multiple conditions.
  • Microsoft 365 and Excel 2021 or later: FILTER can return matching values for another function to calculate.
  • Excel 2003 and earlier: AVERAGEIF and AVERAGEIFS are unavailable.

    Use a compatible array formula or a helper column instead.

Excel formulas may use semicolons rather than commas between arguments, based on regional settings.

If Excel rejects a formula with commas, try replacing the separators with semicolons, such as =AVERAGEIF(A2:A10;"<>0").

Understand how blanks, text, and errors behave

AVERAGEIF ignores blank cells in the average range.

Text entries in the range are generally not counted as numeric values.

However, an error value in the cells being evaluated can cause the formula to return an error, so check the source range if the result is unexpected.

A cell containing a formula that returns zero is still a zero value.

The criterion "<>0" excludes it just as it excludes a zero typed directly into the cell.

If a cell looks blank but contains a formula returning an empty string (""), it is not necessarily equivalent to an actually empty cell in every formula context.

Test your chosen formula against the structure of the data.

Also confirm that the values are numbers rather than numbers stored as text.

Text such as "25" may not be treated like numeric 25 in calculations.

Converting the source data to numbers can resolve unexpected results.

What if the range contains only zeros?

If every numeric value in the range is zero, there are no values left to average after excluding zeros.

AVERAGEIF returns a division-by-zero error, #DIV/0!.

You can display a message instead:

=IFERROR(AVERAGEIF(A2:A10,"<>0"),"No nonzero values")

To keep the result numeric, you might return zero as the fallback:

=IFERROR(AVERAGEIF(A2:A10,"<>0"),0)

Choose a fallback carefully: returning zero can make “no qualifying data” look like a real average of zero.

A message or a separate status cell may communicate the situation more clearly.

Common mistakes to check

  • Using the wrong comparison criterion: The criterion must be text in quotation marks, written as "<>0".
  • Using mismatched ranges with AVERAGEIFS: The average range and each criteria range must cover corresponding rows.
  • Including the wrong cells: Check that the range begins and ends on the intended data rows and excludes headers.
  • Expecting zero to mean missing data in every context: Excluding zeros changes the meaning of the average.

    If zero represents a genuine measurement, removing it may misstate the result.
  • Overlooking errors or text-formatted numbers: Inspect the source cells when the formula returns an error or omits values you expected it to include.

For a straightforward range, start with =AVERAGEIF(A2:A10,"<>0").

Add AVERAGEIFS when you also need criteria such as a category or date, and handle the no-match case if your report must display a friendly result.