How to replace div/0 with a zero

[Jimbo43]

Hi. I am doing some coursework for A-level and I have come across
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/
shows.
There are two main formulas which I need to adjust and have tried bu
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 correc
percentage in certain cells in a table.

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

 

[SteveG]

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

 

[Guest]

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

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]

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 th
first formula the value stays at 0.0% all the time and doesnt show th
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
haven't changed information to make it show this.
Any ideas on why cell F6 shows 0.0%?
Thank

 

[Jimbo43]

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 th
first formula the value stays at 0.0% all the time and doesnt show th
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
haven't changed information to make it show this.
Any ideas on why cell F6 shows 0.0%?
Thank

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

 

[Dav]

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

 

[Jimbo43]

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

I greately appreciate all of your help!!