How to average a range of data ignoring zero in Excel?

Excel is a tool for storing, organizing and managing a large volume of data. It uses rows and columns to store the data. This tool is used for the analysis of data to get insights from it with the use of functions and formulas. Generally, this is used by the financial professional for accounting purposes but it can be used by anyone as it is very easy to use. Data analysis can be done in various ways like visual analysis using graphs, mathematical or trend analysis using formulas, etc. Let’s learn how can we calculate the average of numbers excluding zero values.

How to get an Average of numbers excluding zero values?

Suppose we have the following data values: 10, 20, 0, 0, 10, 20, below table, will show the average of the given data including and excluding zero values:

We can perform the above task using AVERAGEIF() function.

AVERAGEIF() function

This function calculates the average of numbers in a range that meets the criteria as supplied. Function criteria can include logical operators (>,<,<>,=) and wildcards (*,?).

In order to ignore zero values, the criteria supplied is “<>0”, which means “not equal to zero“.

Syntax:

=AVERAGEIF(range, “<>0”)

For the purpose of demonstration, we will use the below-given data to calculate the average ignoring zero values.

Formula applied: AVERAGEIF(range, “<>0”)

Output