replace "#DIV/0!" error with blanks
 

Annika Wrote:
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(ISERROR(OriginalFormula),"",OriginalFormula)

For example:

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

Or put whatever you like between the " "...


--
Mark BPosted from http://www.pcreview.co.uk/ newsgroup access