IS and IF error

Data Validation is useful but imperfect.

It is still possible for users to end up with incorrect values in cells or for you to forget to apply Data Validation to an important input cell.

Excel has a set of Information functions designed to return TRUE if the cell contents are of a particular type and FALSE if not:

As you can see, you could use this to test whether a user has entered a number or a different type of data in a cell. Two functions that you can use to carry out this testing are IS and IF functions.

The IF function allows us to specify the values a cell will take depending on whether a condition is true or false. The IS function, on the other hand, checks the type of value of a cell and returns TRUE or FALSE depending on the outcome.

ISERROR()

ISERROR is a type of IS function that checks a cell for a type of value, in this case any of Excel’s error values. It will return TRUE if the cell referenced contains #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!; otherwise it will return FALSE.

The following table contains two error values: #VALUE! (because cell B4 in the Cost column doesn’t contain a number) and #DIV/0! (because the quantity of 0 in C6 can’t be used to divide the cost). ISERROR() checks the calculated values in column D and marks those containing an error with TRUE:

ISERROR() can be combined effectively with an IF function to hide (or flag) Excel errors. Its syntax is:

=IF(ISERROR([formula or cell to be checked], [result if error], [result if not an error])

Now the Price/unit values that contained an error before have been set to ‘0’ and no longer result in an error of the total:

IFERROR

The function IFERROR can accomplish the same thing as using ISERROR() inside of an IF(). Its syntax omits needing to repeat the value or reference to return if there’s no error:

IFERROR(value,  value_if_error)

This time we’ve specified that if a value in column D results in an error to return the word ‘Review’ as a way to flag the issue:

The next step in the analysis above might be to use conditional formatting, so the results that returned ‘Review’ would be highlighted by a different color font or cell background.

As IFERROR() was introduced in Excel 2007, a potential drawback is that if a user with a lower version of Excel opens a file using this function, IFERROR() won’t work—rather it will just generate the #NAME error. So even though it’s more streamlined than its predecessor, there might be reason for you to consider using IF(ISERROR).

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.