#Div/0 error, can't get rid of it

J

John

Hi Everyone
I use this array formula and it works well,
=IF(COUNT(J5:J36)<1,"",AVERAGE(IF(J5:J36>0,J5:J36,"")))
But if I only have 0 in that column, I get #Div/0, I tried this version
=IF(ISERROR(COUNT($K$5:$K$36)=0),"",AVERAGE(IF($K$5:$K$36>0,$K$5:$K$36,""))) but
to no avail.
Anyone can help me with this.
Thanking you in advance
John
 
J

Joe User

John said:
Hi Everyone
I use this array formula and it works well,
=IF(COUNT(J5:J36)<1,"",AVERAGE(IF(J5:J36>0,J5:J36,"")))
But if I only have 0 in that column, I get #Div/0, I tried this version
=IF(ISERROR(COUNT($K$5:$K$36)=0),"",AVERAGE(IF($K$5:$K$36>0,$K$5:$K$36,"")))
but to no avail.
Anyone can help me with this.
Thanking you in advance
John
 
O

Otto Moehrbach

The error is occurring with the average, where you have a division. Move
the ISERROR to the average. HTH Otto
 
J

Joe User

John said:
=IF(COUNT(J5:J36)<1,"",AVERAGE(IF(J5:J36>0,J5:J36,"")))

Try:

=If(COUNTIF(J5:J36,"<>0")=0, "", AVERAGE(IF(J5:J36>0,J5:J36,"")))


----- original message -----
 
R

Rick Rothstein

Assuming you really mean to average only non-zero numbers, I would use the
array-entered formula...

=IF(ISERROR(AVERAGE(IF(J5:J36>0,J5:J36,""))),"",AVERAGE(IF(J5:J36>0,J5:J36,"")))

where you simply test your functional expression for an error and react to
it.
 
J

Joe User

ERRATA....
=If(COUNTIF(J5:J36,"<>0")=0, "", AVERAGE(IF(J5:J36>0,J5:J36,"")))

Should be:

If(COUNTIF(J5:J36,">0")=0, "", AVERAGE(IF(J5:J36>0,J5:J36,"")))


----- original message -----
 

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

Top