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

[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

 

[Joe User]

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

 

[Otto Moehrbach]

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

 

[Joe User]

=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 -----

 

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

 

[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 -----

 

[John]

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

 

[John]

Thank you Otto
Best Wishes for the Holiday
Regards
John

 

[John]

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