Avoiding the dreaded #div/0 error
 

Can I have advice on how to make the following formulae return a blank
cell instead of a #div/0 error when the first reference cell in the
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 0
displayed. The formula in cell A9 is =E147 and in A13 it is E287. There
is absolutely nothing in E147 or E287. I have tried everything I can
think of to get A9 and A13 to remain blank instead of displaying a 0 (I
may have typed something into E147 and E287 at one time to test it). Any
ideas how to get rid of these zeros?


--
Marie1uk
------------------------------------------------------------------------
Marie1uk's Profile: http://www.excelforum.com/member.php...o&userid=20984
View this thread: http://www.excelforum.com/showthread...hreadid=388599

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"))

Marie1uk wrote:
Can I have advice on how to make the following formulae return a blank
cell instead of a #div/0 error when the first reference cell in the
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 0
displayed. The formula in cell A9 is =E147 and in A13 it is E287. There
is absolutely nothing in E147 or E287. I have tried everything I can
think of to get A9 and A13 to remain blank instead of displaying a 0 (I
may have typed something into E147 and E287 at one time to test it). Any
ideas how to get rid of these zeros?

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=388599

Thanks,

How about these?

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


--
Marie1uk
------------------------------------------------------------------------
Marie1uk's Profile: http://www.excelforum.com/member.php...o&userid=20984
View this thread: http://www.excelforum.com/showthread...hreadid=388599

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" wrote:


Can I have advice on how to make the following formulae return a blank
cell instead of a #div/0 error when the first reference cell in the
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 0
displayed. The formula in cell A9 is =E147 and in A13 it is E287. There
is absolutely nothing in E147 or E287. I have tried everything I can
think of to get A9 and A13 to remain blank instead of displaying a 0 (I
may have typed something into E147 and E287 at one time to test it). Any
ideas how to get rid of these zeros?


--
Marie1uk
------------------------------------------------------------------------
Marie1uk's Profile: http://www.excelforum.com/member.php...o&userid=20984
View this thread: http://www.excelforum.com/showthread...hreadid=388599

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?


--
Marie1uk
------------------------------------------------------------------------
Marie1uk's Profile: http://www.excelforum.com/member.php...o&userid=20984
View this thread: http://www.excelforum.com/showthread...hreadid=388599

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

Vaya con Dios,
Chuck, CABGx3



"Marie1uk" wrote:


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?


--
Marie1uk
------------------------------------------------------------------------
Marie1uk's Profile: http://www.excelforum.com/member.php...o&userid=20984
View this thread: http://www.excelforum.com/showthread...hreadid=388599

Try custom formatting the cell...

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

Hope this helps!

Marie1uk Wrote:
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?


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=388599

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
------------------------------------------------------------------------
Marie1uk's Profile: http://www.excelforum.com/member.php...o&userid=20984
View this thread: http://www.excelforum.com/showthread...hreadid=388599

Any help to corrections to the above formaulae appreciated :)


--
Marie1uk
------------------------------------------------------------------------
Marie1uk's Profile: http://www.excelforum.com/member.php...o&userid=20984
View this thread: http://www.excelforum.com/showthread...hreadid=388599

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

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

Marie1uk wrote:
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?