# #Div/0

Posted by Quang T on December 28, 2001 3:34 PM

Is there any way that #DIV/0 "values" are not displayed? They are kinda tacky looking. Thanks for any reply.

Posted by Jim on December 28, 2001 4:28 PM

Hi Quang,

Try this, change as needed:

=If(B2=0,0,A2/B2)

Jim

Posted by Quang T on December 28, 2001 6:23 PM

Well, I was hoping for some "magic" format or mask to hide these "#DIV/0" cells. I inherited this workbook with 20+ humongous sheets with lots of formulas. I was hoping I wouldn't have to modify all of these existing formulas, even with copy & paste.

Posted by Johnson on December 28, 2001 7:03 PM

This works well, too:

=IF(ISERROR(<yourformula>),"",<yourformula>)

Posted by John on December 28, 2001 9:11 PM

Quang, if have many cells that you need to divide, sometime one cell is missing a number it won't divide, you need a logical test. For example, you have values in A1 and C1, if one number in those cell is missing you get #Div/0. try this if you have only two cells to divide: =IF(A1="","",A1/C1). If hadve more than two cells, you want to try this. =IF(AND(A1="",C1="",F1=""),"",IF(A1="",C1/F1,IF(F1="",A1/C1)))). The AND test you can do up to 30 cells and the IF you do up to seven test. This means that if there's no number in A1,C1, and F1, then leave blank, if A1 is blank then divide C1 by F1 else divide A1 by C1. See if this will give a clue.

: Hi Quang,

Posted by Scott on December 28, 2001 9:19 PM

Just wanted to add: If you actually have the number 0 in your cells, and not just empty cells, you will still get the Div/0 error. However, if you change "" to 0, it should correct both blank and zeros. Example: =IF(B1="","",A1/B1) would change to =IF(B1=0,"",A1/B1) or =IF(B1=0,0,A1/B1)

: Well, I was hoping for some "magic" format or mask to hide these "#DIV/0" cells. I inherited this workbook with 20+ humongous sheets with lots of formulas. I was hoping I wouldn't have to modify all of these existing formulas, even with copy & paste. :

Posted by Bariloche on December 28, 2001 10:23 PM

alternative solution, sorta

Quang,

Given that John Walkenbach has created a utility that "automates" the solution that others have provided you here, I'd say that its pretty certain that Excel has no way of doing what you ask (it would be nice if in the Tools > Options > View there was a checkbox for displaying (or not displaying) error values).

However, you can download John's Power Utility Pak (PUP) and use it for 30 days free. It includes the Error Condition Wizard. It has alot of other interesting items too. You can check out the feature listing here:

http://www.j-walk.com/ss/pup/pup2000/featlist.htm

If you have John's book, then I believe that the PUP is free.

enjoy

Posted by Ivan F Moala on December 29, 2001 4:35 PM

Quang

Try running this macro on it....will change

all formulas wih errors to the format

=IF(ISERROR(expression),"", expression)

'by Dana DeLouis

'This procedure converts normal formulas to those that show an empty cell

'if an error condition exists in the original formula.

Sub ErrorTrapAddDDL()

' Adds =If(IsError() around formulas

Dim cel As Range

Dim rng As Range

Dim Check As String

Const Equ As String = "=IF(ISERROR(_x) ,"""", _x)"

Check = Left$(Equ, 12) & "*" ' Check for =IF(ISERROR(

On Error Resume Next

Set rng = Selection.SpecialCells(xlFormulas, 23)

If rng Is Nothing Then Exit Sub

With WorksheetFunction

For Each cel In rng

If Not cel.Formula Like Check Then

cel.Formula = .Substitute(Equ, "_x", Mid$(cel.Formula, 2))

End If

Next

End With

End Sub

Ivan : Well, I was hoping for some "magic" format or mask to hide these "#DIV/0" cells. I inherited this workbook with 20+ humongous sheets with lots of formulas. I was hoping I wouldn't have to modify all of these existing formulas, even with copy & paste. :

Posted by Escalus on December 29, 2001 4:44 PM

Try this macro ........

Sub Remove_Formula_Errors()

Dim rng As Range, cell As Range, fmla As String

Set rng = Cells.SpecialCells(xlCellTypeFormulas, 16)

For Each cell In rng

fmla = Right(cell.Formula, Len(cell.Formula) - 1)

cell.Formula = "=if(iserror(" & fmla & "), """"," & fmla & ")"

Next

End Sub

Posted by Quang Tran on December 29, 2001 4:49 PM

Re: Try this macro ........

Thanks for all your help. Very kind of everybody.

I used the macro by Escalus and it worked like a charm.