Avoiding the dreaded #div/0 error

[Marie1uk]

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?

[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

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

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]

Thanks,

How about these?

=COUNTIF($H$42:$H$76,"A~*") returns a value of 0 - how do you make 0 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" 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
>
>

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

[Domenic]

Try custom formatting the cell...

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

Hope this helps!

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



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

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

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

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

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

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

[CLR]

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

[Aladin Akyurek]

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

[Aladin Akyurek]

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

[CLR]

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

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

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

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

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

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

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

[CLR]

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

[Aladin Akyurek]

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

[Aladin Akyurek]

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

[CLR]

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

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

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

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

[Aladin Akyurek]

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

[CLR]

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

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

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

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

[Aladin Akyurek]

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

[CLR]

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

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

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

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

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

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

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

[CLR]

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

[Aladin Akyurek]

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

[Aladin Akyurek]

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

[CLR]

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

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

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

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

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

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

[Aladin Akyurek]

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

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

[CLR]

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

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

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

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

[CLR]

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

[Aladin Akyurek]

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

[Aladin Akyurek]

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

[CLR]

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

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

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

[Aladin Akyurek]

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

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

[CLR]

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

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

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

[Aladin Akyurek]

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

[CLR]

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

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

[Jptharocker]

I'm having a similar issue with this dreaded #DIV/0! ...
I would like it to return a 0 or a blank if no value's are inputted... The formula I have is =(COUNTIF(MAR!$G$7:$G$35,"Y")/COUNTIF(MAR!$G$7:$G$35,"*")*100)

Thanks for your help...

[Domenic]

Since you're using Excel 2007, you can use IFERROR...

=IFERROR((COUNTIF(MAR!$G$7:$G$35,"Y")/COUNTIF(MAR!$G$7:$G$35,"*")*100),"")

[protonLeah]

@Jptharocker: This thread is seven years old.
Forum rule #2: Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.