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 they be considered in the average i.e. they are part of the calculation or should they be ignored i.e. the item is there but you want Excel to determine an average without taking that item into account.

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