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.
Download Sample File
More Formulas
- Calculate Compound Interest in Excel
- Calculate Cube Root in Excel
- Calculate Percentage Variance (Difference)
- Calculate Simple Interest in Excel
- Calculate SQUARE ROOT in Excel
- Calculate the Weighted Average in Excel
- Does Not Equal Operator in Excel
- Round a Number to Nearest 1000, 100, and 10
- Round to Nearest .5, 5. 50 (Down-Up) in Excel
- Square a Number in Excel