How to replace div/0 with a zero
 

Hi. I am doing some coursework for A-level and I have come across a
problem where a 0 value is represented by a #div/0! instead of the 0.0%
which I would like.
When a cell that the chart refers to is changed sometimes the div/0
shows.
There are two main formulas which I need to adjust and have tried but
to no success:
=SUMPRODUCT(--($B$30:$B$169="F"),--($D$30:$D$169="A"))/COUNT($F$30:$F$169)

and

=COUNTIF($D$30:$D$169,"A")/COUNT($F$30:$F$169)

Both these formulas are used various times to calculate a correct
percentage in certain cells in a table.

Can anyone tell me what to do with the formula to get rid of the Div/0
error and show 0/0.0%?
I would greatly appreciate any help. Thanks a lot!


--
Jimbo43
------------------------------------------------------------------------
Jimbo43's Profile: http://www.excelforum.com/member.php...o&userid=31991
View this thread: http://www.excelforum.com/showthread...hreadid=517371

How to replace div/0 with a zero
 

Try adding a condition for the error.

=IF(ISERROR(SUMPRODUCT(--($B$30:$B$169="F"),--($D$30:$D$169="A"))/COUNT($F$30:$F$169)),0,SUMPRODUCT(--($B$30:$B$169="F"),--($D$30:$D$169="A"))/COUNT($F$30:$F$169))

Apply in the same manner to your other formula.

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=517371

How to replace div/0 with a zero
 

=IF(COUNT($F$30:$F$169)=0,"WhateverMessageYouWant" ,SUMPRODUCT(--($B$30:$B$169="F"),--($D$30:$D$169="A"))/COUNT($F$30:$F$169))

=IF(COUNT($F$30:$F$169)=0,"WhateverMessageYouWant" ,COUNTIF($D$30:$D$169,"A")/COUNT($F$30:$F$169))

hth
Vaya con Dios,
Chuck, CABGx3


"Jimbo43" wrote:


Hi. I am doing some coursework for A-level and I have come across a
problem where a 0 value is represented by a #div/0! instead of the 0.0%
which I would like.
When a cell that the chart refers to is changed sometimes the div/0
shows.
There are two main formulas which I need to adjust and have tried but
to no success:
=SUMPRODUCT(--($B$30:$B$169="F"),--($D$30:$D$169="A"))/COUNT($F$30:$F$169)

and

=COUNTIF($D$30:$D$169,"A")/COUNT($F$30:$F$169)

Both these formulas are used various times to calculate a correct
percentage in certain cells in a table.

Can anyone tell me what to do with the formula to get rid of the Div/0
error and show 0/0.0%?
I would greatly appreciate any help. Thanks a lot!


--
Jimbo43
------------------------------------------------------------------------
Jimbo43's Profile: http://www.excelforum.com/member.php...o&userid=31991
View this thread: http://www.excelforum.com/showthread...hreadid=517371

How to replace div/0 with a zero
 

Yes, that works thanks so much! Tricky to get right are these formulas.
For the second formula:
=COUNTIF($D$30:$D$169,"A")/COUNT($F$30:$F$169)
I changed that to
=IF(ISERROR(COUNTIF($D$30:$D$169,"A")/COUNT($F$30:$F$169),
0,COUNTIF($D$30:$D$169,"A")/COUNT($F$30:$F$169) but it says theres an
error with the formula?


--
Jimbo43
------------------------------------------------------------------------
Jimbo43's Profile: http://www.excelforum.com/member.php...o&userid=31991
View this thread: http://www.excelforum.com/showthread...hreadid=517371

How to replace div/0 with a zero
 

Thanks for both your replies. I've tried all the ways and they all work,
the second formula is sorted. However, I find that when I change the
first formula the value stays at 0.0% all the time and doesnt show the
correct percentage.
This is a screenshot of my system.
Cell F6 refers to the first formula and the second formula is H6.
Obviously on this shot there aren't all the Div/0 errors since I
haven't changed information to make it show this.
Any ideas on why cell F6 shows 0.0%?
Thanks


--
Jimbo43
------------------------------------------------------------------------
Jimbo43's Profile: http://www.excelforum.com/member.php...o&userid=31991
View this thread: http://www.excelforum.com/showthread...hreadid=517371

How to replace div/0 with a zero
 

Thanks for both your replies. I've tried all the ways and they all work,
the second formula is sorted. However, I find that when I change the
first formula the value stays at 0.0% all the time and doesnt show the
correct percentage.
This is a screenshot of my system.
Cell F6 refers to the first formula and the second formula is H6.
Obviously on this shot there aren't all the Div/0 errors since I
haven't changed information to make it show this.
Any ideas on why cell F6 shows 0.0%?
Thanks


+-------------------------------------------------------------------+
|Filename: A!.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4398 |
+-------------------------------------------------------------------+

--
Jimbo43
------------------------------------------------------------------------
Jimbo43's Profile: http://www.excelforum.com/member.php...o&userid=31991
View this thread: http://www.excelforum.com/showthread...hreadid=517371

How to replace div/0 with a zero
 

Yes, that works thanks so much! Tricky to get right are these formulas.
For the second formula:
=COUNTIF($D$30:$D$169,"A")/COUNT($F$30:$F$169)
I changed that to
=IF(ISERROR(COUNTIF($D$30:$D$169,"A")/COUNT($F$30:$F$169),
0,COUNTIF($D$30:$D$169,"A")/COUNT($F$30:$F$169) but it says theres an
error with the formula?

Are you brackets in the correct places?

I changed that to
=IF(ISERROR(COUNTIF($D$30:$D$169,"A")/COUNT($F$30:$F$169)),
0,COUNTIF($D$30:$D$169,"A")/COUNT($F$30:$F$169) but it says theres an
error with the formula?

or even if the error is just dividing by 0

if(
COUNT($F$30:$F$169)0,COUNTIF($D$30:$D$169,"A")/COUNT($F$30:$F$169),0)

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=517371

How to replace div/0 with a zero
 

Thanks a lot for all your super speedy replies. It was my mistake, I
made an error. Everything is fixed and working :D :D

I greately appreciate all of your help!!


--
Jimbo43
------------------------------------------------------------------------
Jimbo43's Profile: http://www.excelforum.com/member.php...o&userid=31991
View this thread: http://www.excelforum.com/showthread...hreadid=517371