dealing with #n/a #div0 etc

[macroapa]

Hi,

I have a nice dashboard built in excel, but a lot of the results can
produce 'errors' (ie when 0 is divided by 0).

Is there a way to auto hide such errors?

I know I can use:

If(ISERROR(a1/a2),"",a1/a2))

but this is very onerous to have to put this on all the formulas
(especially as some forumlas are quite long and complex).

I have also considered 'conditonal formatting', but this also isnt
practical as I already have CF with 3 conditions (ie to highlight if a
result is Red/Amber/Green against a target).

Are there any other options (XL2002).

Thanks.

 

[muddan madhu]

try this

select the cell go to data | format | conditional format | condition
1: formula = =ISERROR(B1) | click format | font tab | color : white |
ok | ok

 

[Gord Dibben]

It would be best to write your formulas to eliminate the errors but you
could use this macro to add the ISERROR function to all in place formulas.

Note: ISERROR masks all errors and may mask an error you should be aware
of.

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