Avoiding the dreaded #div/0 error

[Marie1uk]

Can I have advice on how to make the following formulae return a blan
cell instead of a #div/0 error when the first reference cell in th
formula is blank or 0?

=E16/COUNTIF($C$29:$C$33,"M")

in cell J21: =COUNTIF($H$42:$H$426,"A~*")/COUNT($J$42:$J$426)

in cell F25: =SUM(J21:J24)

Another rather annoying thing is that in cell A9 and A13 there is a
displayed. The formula in cell A9 is =E147 and in A13 it is E287. Ther
is absolutely nothing in E147 or E287. I have tried everything I ca
think of to get A9 and A13 to remain blank instead of displaying a 0 (
may have typed something into E147 and E287 at one time to test it). An
ideas how to get rid of these zeros

 

[Aladin Akyurek]

Hard to match qua refernces the verbal description you give with the
formulas you post. That said:

=E16/COUNTIF($C$29:$C$33,"M")

can be re-expressed to avoid #DIV/0! as

=E16/MAX(1,COUNTIF($C$29:$C$33,"M"))

 

[mangesh_yadav]

To check for blanks, use:
=IF(ISBLANK(A1),"",A1)

And to avoid div0 error,
=IF(ISERR(E16/COUNTIF($C$29:$C$33,"M")),"",E16/COUNTIF($C$29:$C$33,"M"))
puts a blank instead of DIV0 error

Mangesh

 

[Marie1uk]

Thanks,

How about these?

=COUNTIF($H$42:$H$76,"A~*") returns a value of 0 - how do you make
values stay blank

 

[CLR]

Others have answered the first part of your post.

As for the second part.....use one of these, replacing A1 with your cell
addresses.

=IF(A1=0,"",A1), or =IF(A1="","",A1)

Vaya con Dios,
Chuck, CABGx3

 

[Marie1uk]

Maybe I was unclear. This formula:

=COUNTIF($H$42:$H$76,"A~*") returns a value of 0 - how do I modify this
formula to return a blank instead of a 0 value?

 

[CLR]

=IF(COUNTIF($H$42:$H$76,"A~*")=0,"",COUNTIF($H$42:$H$76,"A~*"))

Vaya con Dios,
Chuck, CABGx3

 

[Domenic]

Try custom formatting the cell...

Format > Cell > Number > Custom > Type: 0;-0;;@

Hope this helps!

 

[Marie1uk]

These formulae are returning a DIV/0 error,

=COUNTIF($H$42:$H$426,"A~*")/COUNT($J$42:$J$426)

and

=E9/COUNTA(A29:A33)

How would I prevent them from returning a DIV/0 error plz

 

[Marie1uk]

Any help to corrections to the above formaulae appreciated :

 

[Aladin Akyurek]

=COUNTIF($H$42:$H$426,"A~*")/MAX(1,COUNT($J$42:$J$426))

=E9/MAX(1,COUNTA(A29:A33))