Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace #DIV/0 error with zeros | Excel Discussion (Misc queries) | |||
replace "#DIV/0!" error with blanks | Excel Worksheet Functions | |||
Search and replace | Excel Worksheet Functions | |||
replace absolute references | Excel Worksheet Functions | |||
VB Find and Replace | Excel Worksheet Functions |