Replace "#DIV/0!" with ""

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

Annika

I am in need of some expert advice!!
I work with large sets of data, which are then compiled by
doing averages.

Occasionally I get a #DIV/0! error, when the range to be
averaged refers to a bunch of blank cells.

I need to be able to Find and Replace those with blank
cells, but using the Find and Replace commands under the
Edit menu won't do anything.
I have also written macros to try and solve this problem,
but they are also unable to correct the problem. Does
anyone know a shortcut on this one? Emptying the erroneous
cells manually is not an option, since the data sets are
so large.
Please e-mail me with any ideas or help you may be able to
offer. I am running on Excel 2000, and my windows platform
is WindowsXP Pro.
 
Annika,

Try some code like this

For Each cell In Selection
If cell.Text = "#DIV/0!" Then
cell.Value = ""
End If
Next
 
=IF(ISERR(your formula),0,(your formula))

would return a 0 if your current formula results in a #DIV/0 or an
other error except #N/A. Otherwise, it returns the result of you
formula
 
Back
Top