Ignore zeros in average

When you use the average function in Excel, it is important to decide what you want to do with zeros. Should Excel ignore zeros in average or should they be considered?

An example may be telephone records. If you want to get the average cost of calls, do you include the voicemail calls which are free? If you do include them, it will look like your average call is lower, but is a voicemail call a real call? Don’t you want to know the average of calls that you pay for? Either way can be done in Excel. If you are happy with including zero’s then use the average function.

Ignore zeros in average

However, in order to remove zeros from the average you will need to use the AVERAGEIF function. As shown below, with the AverageIf you need to tell it where to look AND what must be included/ excluded i.e.

=AVERAGEIF(  $E$3:$E$341  ,  “<>0”  )

It is important to remember to put the ‘not equal to 0’ (<>0) in inverted commas for it to work.

As you will see below, a normal AVERAGE would suggest R2.71 per call, whereas the actual average of paid for calls is R3.30. This avoids that case where excessive calls to free voicemail, hides the true average call costs.

Ignore zeros in average


Showing zeros in Excel

Find the max number ignoring errors in Excel

Find first non zero number in a list

IF not blank then calculate in Excel