#DIV/0! error

D

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)
 
D

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].
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

iserror problem 10
#div/0 error 2
#DIV/0! in a Pivot Table 1
Need a formula 0
Need "Div/0" Average Relief ... 4
how to average a range of cells and ignore #div/0! answer 4
Another DIV/0 Error 2
Zeros and #DIV/0! 5

Top