How to Average Number but Exclude Zeros (0) in Excel (Formula)

Last Updated: March 24, 2024
puneet-gogia-excel-champs

- Written by Puneet

When you average values in Excel, you can write a formula in a way to exclude or ignore cells with a zero. In this tutorial, we will look at the way to write this average formula.

average-value-in-cells

Ignore Zero while Averaging Values in Excel

You can use the below steps:

  1. First, enter the function AVERAGEIF in a cell.
  2. After that, in the first argument, refer to the range where you have values.
  3. Now, in the second argument, enter “>0”. This tells Excel to only include values greater than zero.
  4. In the end, enter closing parentheses and hit enter to get the result.
averageif-to-ignore-zeros

You don’t need to specify the average_range which is an optional argument. When you skip specifying it, AVERAGEIF average values from the range that you specify in the range criteria.

Below you can see the difference between the average result when you exclude zeros and when you include zeros.

average-result-when-exclude-zero

Get the Excel File