MrExcel Publishing
Your One Stop for Excel Tips & Solutions

without useing ISERROR or CONDITION FORMAT...


Posted by JOHN S. on March 02, 2001 7:14 AM

Without using ISERROR or CONDITIONAL FORMATING how can I clear those unsightly errors form my my reports and still be able to sum up columns with errors at the bottom


Posted by GregA on March 02, 2001 8:27 AM

Stop them at the source

The best way is to stop the error at the source, where the initial error condition is produced. Errors tend to multiply, since cells that refer to an error show an error, and so on. Often it is just a few cells that are the source of the errors, so if you stop these few from generating errors, you will eliminate all the errors on your sheet.

For example, if you have a few cells that might perform division by zero, you can catch the errors with (assume A1 is the denominator cell that might cause divide-by-zero):

=IF(A1=0,"",<your formula>)

This will return a blank if A1 is zero, or the formula (which divides by A1) if A1 is non-zero.

Let me know if there are specific error-causing situations you need help dealing with.

Greg

Posted by GregA on March 02, 2001 8:28 AM

REFORMATTED: Stop them at the source

The best way is to stop the error at the source, where the initial error condition is produced. Errors tend to multiply, since cells that refer to an error show an error, and so on. Often it is just a few cells that are the source of the errors, so if you stop these few from generating errors, you will eliminate all the errors on your sheet.

For example, if you have a few cells that might perform division by zero, you can catch the errors with (assume A1 is the denominator cell that might cause divide-by-zero):

=IF(A1=0,"",[your formula])

This will return a blank if A1 is zero, or the formula (which divides by A1) if A1 is non-zero.

Let me know if there are specific error-causing situations you need help dealing with.

Greg