replace "#DIV/0!" errors with blanks

  • Thread starter Thread starter Annika
  • Start date Start date
A

Annika

Does anyone know how to replace #DIV/0! errors with blanks
automatically?

I am working with very large data sets, and complete
averages, maximums, etc when compiling the data. However,
the #DIV/0! errors always affect each subsequent
calculations.

I have tried using the Find and Replace commands under the
Edit menu, and have also attempted formulas such as:

=IF(AVERAGE(A12:A19)="#DIV/0!", "", AVERAGE(a12:a19))

Both of these methods leave me with the same "#DIV/0!" as
before. As you can see, this will negatively affect any
calculations based on the cells containing these errors.

Taking the errors out manually is not an option, since the
data sets are so very large.

Please send me any ideas, etc. that you might have!
My windows platform is Windows XP Pro, and I am using
Excel 2000. I am fairly comfortable with VBA, so if your
answer involves any of that, please feel free.
 
=IF(AVERAGE(A12:A19)="#DIV/0!", "", AVERAGE(a12:a19))

....is incorrect. Use

=IF(ISERROR(AVERAGE(A12:A19)), "", AVERAGE(a12:a19))

Regards,
Aaron Queenan.
 
Annika

Here is same answer I gave yesterday to your same question. Are you not
seeing replies to your posts? Get a real newsreader and forget the CDO.

Annika

I assume you don't want to wipe out formulas, just hide the #DIV/0!

Run this code on your selected range.

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 Excel MVP - XL97 SR2 & XL2002
 
Back
Top