replace "#DIV/0!" errors with blanks

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

 

[Aaron Queenan]

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

....is incorrect. Use

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

Regards,
Aaron Queenan.

 

[Gord Dibben]

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