#DIV/0! error

[dbconn]

I have the following formula entered to compute the average of a range of
cells and discount zeros. Until I change one of the zeros to a number, I get
#DIV/0!, How do I get rid of this

My formula is: =AVERAGE(Q8:Q18,"<>0",Q8:Q18)

 

[David Biddulph]

That formula will not give you either your required value or a #DIV/0!
error. It will return #VALUE! because one of the 23 values which you have
asked it to average is the text string "<>0". The other 22 numbers which
you are asking it to average are two instances each of the elements in the
array Q8:Q18.

Perhaps you have fallen into the trap of typing into the newsgroup the
formula that you thought you were using, rather than copying the actual
formula from the formula bar and pasting it here.

The formula which you may have intended might have been the *array* formula
=AVERAGE(IF(Q8:Q18<>0,Q8:Q18,"")) [entered with Control Shift Enter]. That
formula will indeed return #DIV/0! if there are no non=zero numbers in the
array Q8:Q18, so you may want something like
=IF(COUNT(Q8:Q18)-COUNTIF(Q8:Q18,0)=0,"",AVERAGE(IF(Q8:Q18<>0,Q8:Q18,"")))
[again as an array formula].