Exclude zeros while calculating the average in Excel. This method removes all zeros from the equation.
We have two methods for Calculation:
Method 1 : For Excel 2007 and later versions:
Select the range of cells, for which you want to calculate
Average and use below function:
=AVERAGEIF(A1:A5,"<>0")
A1:A5 change this to the range that you want to average and that's it.
"
<>0" is the part that tells the function to ignore cells that have zeros in them.
Result:
Method 2 : For Excel 2003 and older versions:
If you have
Excel 2003 or earlier, you must use this version of the formula.
Select the range of cells, for which you want to calculate Average and use below function:
=AVERAGE(IF(A1:A5<>0,A1:A5))
Array Formula - this is an array formula so you must enter it using
Ctrl +
Shift +
Enter.
A1:A5 is the range that you want to average; make sure to change it in both parts of the formula to work with your data.
Result:
If you don't enter this formula correctly, you will see 2.4 as a result.