On UrbanPro

View My Profile       Proud to be a Teacher       Write a Review  

Thursday, January 12, 2017

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.