Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA | Excel Worksheet Functions | |||
How do I change uppercase to proper case in entire worksheet? | Excel Worksheet Functions | |||
Search/Match between 2 x separate Worksheets and populate result in third worksheet | Excel Discussion (Misc queries) | |||
Is there a formula to spell out a number in excel? | Excel Worksheet Functions | |||
Convert Numeric into Text | Excel Worksheet Functions |