#Div/0

Hi Quang,

Try this, change as needed:

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

Jim

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.

This works well, too:

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

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,

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. :

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

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. :

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

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.