replace "#DIV/0!" error 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]

Please only post to one newsgroup OR post to multiple groups in ONE message!

 

[Allan Rogg]

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

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.

Try

=IF(COUNT(A12:A19)=0," ",AVERAGE(A12:A19))

Allan Rogg

 

[cf]

Annika,

You may want to use a conditional format. First, format
the cell to use white font (or the background color of
the cell). Then use a conditional format that states if
Cell is > zero, font color black (or whatever color you
want).

Hope this solves you problem.

a similar problem cropped up for me today ...

and we had also tried the two options of conditional formatting with white ...
which still screwed up our formulae ...

the same problem resulted when making do with the "" return for one of the IF
conditions ...

it still doesn't return a blank cell ...

ISBLANK() on that cell still returns FALSE indicating that the cell is not
blank ... but just appears to be ...

is there an alternative method ... ???

cf

 

[Mark B]

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.

=IF(ISERROR(OriginalFormula),"",OriginalFormula)

For example:

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

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