Average Ignore 0 – Excel & Google Sheets
Written by
Reviewed by
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.
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:
=AVERAGEIF(C3:C7,"<>0")
Ignore Text and Blank Values with AVERAGE Function
The AVERAGE and AVERAGEIF Functions ignore text and blank values.
This example demonstrates this::
=AVERAGE(C3:C7)
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(C3:C7)
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.
