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 validate the data in a specific cell or set of cells and return TRUE if the cell contents are of a particular type and FALSE if not.
These functions are known as IS functions:
A simple example of IS Functions is ISNUMBER, which returns TRUE if the cell referred to is a number; otherwise it returns FALSE. Therefore:
returns FALSE below because that cell contains text:
The function ISTEXT works the same way:
Like ISNUMBER() and ISTEXT() introduced in the previous module, ISERROR is another 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:
The ISBLANK function returns the logical value TRUE if the value argument is a reference to an empty cell; otherwise it returns FALSE.
You can use an IS function to get information about a value before performing a calculation or other action with it. For example, you can use the ISERROR function in conjunction with the IF function to perform a different action if an error occurs:
=IF(ISERROR(A1), “An error occurred.”, A1 * 2)
This formula checks to see if an error condition exists in A1. If so, the IF function returns the message “An error occurred.” If no error exists, the IF function performs the calculation A1*2.
Below is a list of all of the possible is functions available in Excel:
|Function||Returns TRUE if|
|ISBLANK||Value refers to an empty cell.|
|ISERR||Value refers to any error value except #N/A.|
|ISERROR||Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).|
|ISLOGICAL||Value refers to a logical value.|
|ISNA||Value refers to the #N/A (value not available) error value.|
|ISNONTEXT||Value refers to any item that is not text. (Note that this function returns TRUE if the value refers to a blank cell.)|
|ISNUMBER||Value refers to a number.|
|ISREF||Value refers to a reference.|
|ISTEXT||Value refers to text.|
Paolo Lenotti is the Head of Marketing & PR at Filtered.com.