Excel giving wrong average

Question

Hi I have an average function:

    =IF(ISERROR(AVERAGE(H6:H31)), "", AVERAGE(H6:H31))

but it returns the wrong average for the numbers: 0, 0, 3, 0, 0, 0, 0, 0, 4, 0

It produces 0.7 instead of 3.5 and I am definitely using column H row 6 to 31

What could cause this? Thanks

Answer 1

0.7 is the correct answer. See here

You are looking for the average excluding zeros. In which case you should use the AVERAGEIF function. In your case that would be:

=AVERAGEIF(H6:H31,"<>0")

This will give you 3.5

Answer 2

Average is considered as

Grand Total / Total no of Obs.

In this case you have total of 10 observations.

so, 7/10 is 0.7