Return to Excel Formulas List

Average Ignore 0 – Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to calculate the average of a set of numbers while ignoring zero values in Excel and Google Sheets.

Average Ignore 0 in Excel


The AVERAGE Functions

The AVERAGE Function calculates the average of a set of numbers, including zeros. To calculate the average, ignoring zeros, you can use the AVERAGEIF Function.

Ignore Zero Values With AVERAGEIF Function

This example calculates the Average Score of 5 teams while ignoring Scores with a value of 0:



Ignore Text and Blank Values with AVERAGE Function

The AVERAGE and AVERAGEIF Functions ignore text and blank values.

This example demonstrates this::

AVERAGE Text and Blank


Treat Text Values as Zero With AVERAGEA Function

In order to treat text values as zero in the calculation of the average of a list of values, the AVERAGEA Function can be used:

AVERAGEA Text and Blank


As with the AVERAGE and AVERAGEIF Functions, blank cells are ignored by the AVERAGEA Function

Ignore Zero Values in Google Sheets

These formulas work in exactly the same way in Google Sheets as in Excel.

average ignore 0 Google Function