How to change #DIV/0! result for an entire worksheet ?
 

The worksheet I created has many forumlas where the 'divisor' can be zero.
The formulae are long and complicated with nested functions. So, I don't
want to add the If statement for a zero divisor to each cell that can result
in a DIV/0.
Is there a way that I can for an entire spread sheet at once modify the
result from DIV/0 to blank?

How to change #DIV/0! result for an entire worksheet ?
 

Use number formatting

like : 0.00,,,""
or General,,,""

it goes like: <positive value,<negative value,<zero value,<error value

"LG" wrote:

The worksheet I created has many forumlas where the 'divisor' can be zero.
The formulae are long and complicated with nested functions. So, I don't
want to add the If statement for a zero divisor to each cell that can result
in a DIV/0.
Is there a way that I can for an entire spread sheet at once modify the
result from DIV/0 to blank?

How to change #DIV/0! result for an entire worksheet ?
 

Maybe you can use format|conditional format to hide the error (white font on
white fill)???

=iserror(a1)
would be how I'd use that conditional format formula (for A1)

LG wrote:

The worksheet I created has many forumlas where the 'divisor' can be zero.
The formulae are long and complicated with nested functions. So, I don't
want to add the If statement for a zero divisor to each cell that can result
in a DIV/0.
Is there a way that I can for an entire spread sheet at once modify the
result from DIV/0 to blank?

--

Dave Peterson

How to change #DIV/0! result for an entire worksheet ?
 

Did that really work for you?

DarkByte wrote:

Use number formatting

like : 0.00,,,""
or General,,,""

it goes like: <positive value,<negative value,<zero value,<error value

"LG" wrote:

The worksheet I created has many forumlas where the 'divisor' can be zero.
The formulae are long and complicated with nested functions. So, I don't
want to add the If statement for a zero divisor to each cell that can result
in a DIV/0.
Is there a way that I can for an entire spread sheet at once modify the
result from DIV/0 to blank?



--

Dave Peterson

How to change #DIV/0! result for an entire worksheet ?
 

LG

Can you use a macro?

Select all cells in question and run the macro.

Sub ErrorTrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISERROR*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISERROR(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Fri, 26 May 2006 07:52:02 -0700, LG wrote:

The worksheet I created has many forumlas where the 'divisor' can be zero.
The formulae are long and complicated with nested functions. So, I don't
want to add the If statement for a zero divisor to each cell that can result
in a DIV/0.
Is there a way that I can for an entire spread sheet at once modify the
result from DIV/0 to blank?