Ignore Zero when Calculating the Average Number in Excel

If you ever get stuck and think that you cannot resolve a certain issue with Excel, just remember that combining formulas can be a great solution to your problem.

Such is the case when dealing with zeroes in Excel. In the example below, we will show how to ignore zeros in the calculation of the average.

Ignore Zero When Calculating the Average

For our example, we will use the list of random numbers:

To calculate the average of this list, we would only need to insert the AVERAGE formula in cell D2 and use our list:

And will get the following results:

To disregard the zeros that we have in our list, we need to add IF to our formula, and the formula will be:

With this condition added, we will get the following results:

Having in mind that the average is a quotient of the SUM of a certain range and the COUNT of that same range, we can use these two formulas together with the condition and calculate our average.

The formula in cell F2 will be as follows:

And will end up with the same result:

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.