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.
Ignore Zero while Averaging Values in Excel
You can use the below steps:
- First, enter the function AVERAGEIF in a cell.
- After that, in the first argument, refer to the range where you have values.
- Now, in the second argument, enter “>0”. This tells Excel to only include values greater than zero.
- In the end, enter closing parentheses and hit enter to get the result.
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.