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

J

#### John

Thank you Rick
Works like a charm.
Best wishes for the Holiday
Regards
John

J

#### John

Thank you Otto
Best Wishes for the Holiday
Regards
John

J

#### John

Hi Joe
This one works well, thank you
Best wishes for the Holiday
Regards
John