Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
=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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
=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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I prevent a #DIV/0! error from plotting on a chart | Charts and Charting in Excel | |||
replace "#DIV/0!" error with blanks | Excel Worksheet Functions | |||
# DIV/0! error in Excel | Excel Discussion (Misc queries) | |||
"Average" with error DIV/0 | Excel Discussion (Misc queries) | |||
#DIV/0 Error | Excel Worksheet Functions |