Thursday, October 22, 2009

Information Functions - ISERROR and others

Information functions
ISBLANK, ISNUMBERand ISTEXTas well as ISERROR are information functions and are usually found nested in other functions such as an IF since they do little by themselves. The three functions return either a True or False, which can be used in other calculations to continue a calculation or decision.

When you first look at Information functions, you wonder - why bother. However, if you import a lot of data you may find them useful. And if you nest an information function within an IF Function then it can be very powerful.

Below are a couple of the more useful Information functions. By themselves, they test a cell and return a result of either True or False

The ISBLANK function tests whether a cell is empty and returns a value of TRUE if it is empty. =ISBLANK (cell reference)

The ISNUMBER function tests a cell's value to determine if it is a number. If the contents of the cell are a number then it is true
otherwise it is false. =ISNUMBER (cell reference)

ISTEXT tests whether the contents of the cell are text and return true.
=ISTEXT (cell reference)

The most common use is combining =ISERROR() with an IF to replace errors in cells with a blank. The IFERROR() function that is available in Excel 2007 reduces the need for this but if you have Excel 2003 you definitely need this.
Below is an example, the ISERROR checks to see if cell the formula C9/D9 will result in an error. If this is TRUE, then the error is replaced with a blank cell - otherwise it is FALSE and the result of C9/D9 is displayed in the cell.

When using an Information function with an IF Function:
Put the TRUE and FALSE components in first and then enter the information into the test last otherwise the dialog box collapses on you and have to manually type in the formulas. This is particularly true of Excel 2003.