Calculate Average Excluding Zeros in Excel

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.