Tuesday, November 30, 2010

How do you avoid displaying errors in Excel spreadsheet formulas?

How do you avoid errors in Excel formulas? You don't need an online degree to figure this one out, but I do assume you have a basic understanding of simple logic formulas such as IF(). Sometimes, when you create a new formula you may get an error message in return (Here's a complete list of errors you may see in Excel: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!). In some cases, you'll want to know when a formula error occurs, but more often than not you would rather avoid these messages. You can do so by using an IF() function to check for an error. Use this standard format:

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

For example, the formula below displays a blank if the division results in an error.

=IF(ISERROR(A1/B1),"",A1/B1)