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 such as 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.

Suppose we have the following data values: 10, 20, 0, 0, 10, 20. The table below 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:

In conclusion, learning how to average a range of data while ignoring zeros in Excel is crucial for accurate calculations. This article has shown you how to use the AVERAGEIF function to achieve this effectively.

How to average in Excel excluding zero?

To average in Excel excluding zeros:

1. Use the AVERAGEIF function.
2. Specify the range.
3. Set criteria as “<>0”.
4. Press Enter.
5. Excel will calculate the average excluding zeros.

What is the formula for average not including 0?

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