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

  • Thread starter Thread starter John
  • Start date Start date
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
 
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
Click to expand...
 
The error is occurring with the average, where you have a division. Move
the ISERROR to the average. HTH Otto
 
John said:
=IF(COUNT(J5:J36)<1,"",AVERAGE(IF(J5:J36>0,J5:J36,"")))
Click to expand...

Try:

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


----- original message -----
 
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.
 
ERRATA....
=If(COUNTIF(J5:J36,"<>0")=0, "", AVERAGE(IF(J5:J36>0,J5:J36,"")))
Click to expand...

Should be:

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


----- original message -----
 
Back
Top