The IS Functions return a TRUE or FALSE value based upon a condition they evaluate. They can detect errors, blank cells, text, numbers...etc. They are critical in the development of dashboard interfaces and are heavily used with the IF function.
The IS functions are a set of computer programs that you run from a worksheet cell formula. Their job is to return a TRUE or FALSE value based upon a condition they evaluate. These functions are basically your worksheet cell value and worksheet formula error testers in logic. Here is a list of the IS functions and what they do:
|ISBLANK Function||Returns TRUE if its argument evaluates to blank, for example an empty cell reference|
|ISERR Function||Detects an error in a formula used as its argument and returns TRUE if detected, it ignores the #N/A error value|
|ISERROR Function||Detects an error in a formula used as its argument and returns TRUE if detected, covers all errors (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!)|
|ISLOGICAL Function||Detects if its argument has the value of TRUE or FALSE and returns TRUE if so|
|ISNA Function||Detects an #N/A error in a formula used as its argument and returns TRUE if detected. It only detects #N/A|
|ISNONTEXT Function||Detects if its argument is not a text value and returns TRUE if so (Note that this function returns TRUE if its argument refers to a blank cell)|
|ISNUMBER Function||Detects if its argument is a number and returns TRUE if so|
|ISREF Function||Detects if its argument produces a value that can be evaluated as a cell or range reference and returns TRUE if so|
|ISTEXT Function||Detects if its argument produces a text value and returns TRUE if so|
The IS functions can be used alone in a worksheet formula as a flagging device when processing data on a worksheet. For example, say you have rows of data on a worksheet and you wanted to know if any of those rows had a number in a particular column. You could use the ISNUMBER function to evaluate that case and return TRUE/FALSE. You could then use Excel's AutoFilter to filter on the TRUE/FALSE values returned. The IS worksheet functions are also extremely useful when developing error traps on your worksheet thus preventing your worksheet formulas from generating errors a user can see. They are also useful when designing dashboards especially interpreting what a user has typed in a cell so a formula can adjust to the input. They are very commonly used as a nested function to the first argument of the IF function especially in the cases of error protection and user value interpretation (what the user types in a cell). The AND and OR functions also heavily use these functions as nested functions in their argument list. A complement to these functions is also the Excel Data Validation feature which also helps protect against the user typing the wrong values on a worksheet. Remember, locking down what the user can type is always more effective then trying to have a formula figure out what they typed. You run the IS worksheet functions by typing their names in a formula then followed by the information they are suppose to analyze.
Whenever you type a formula in a worksheet cell, this is called syntax or grammar. The general syntax of the IS functions are like this when you type them in a worksheet cell:
|Function Name||Argument Purpose|
|= ISBLANK( value )||The value argument can be a cell reference, range reference or formula that identifies a reference. If the cell or range is blank, this function returns TRUE|
|= ISERR( value )||The value argument can be a cell reference, range reference or formula. If a calculation error is produced excluding #N/A, this function returns TRUE|
|= ISERROR( value )||The value argument can be a cell reference, range reference or formula. If a calculation error is produced including #N/A, this function returns TRUE|
|= ISLOGICAL( value )||The value argument can be a constant, cell reference, range reference or formula. If they evaluate to TRUE or FALSE, this function returns TRUE|
|= ISNA( value )||The value argument can be a cell reference, range reference or formula. If a calculation error is produced generating #N/A, this function returns TRUE|
|= ISNONTEXT( value )||The value argument can be a constant, cell reference, range reference or formula. If argument evaluates to a non-text value, this function returns TRUE (Note that this function returns TRUE if the value refers to a blank cell)|
|= ISNUMBER( value )||The value argument can be a constant, cell reference, range reference or formula. If argument evaluates to a number value, this function returns TRUE (Note that this function returns FALSE if the value refers to a blank cell)|
|= ISREF( value )||The value argument can be a formula. If argument evaluates to an Excel range or cell reference, this function returns TRUE (check out the INDIRECT function)|
|= ISTEXT( value )||The value argument can be a constant, cell reference, range reference or formula. If argument evaluates to a text value, this function returns TRUE (Note that this function returns FALSE if the value refers to a blank cell)|
Where value is called the function argument list. Remember, you are running a computer program at this point so the program needs information to operate and that is why there is an argument list. When you see an argument list and you see square brackets [ ] around the argument name, this means the argument is optional and you do not have to include it when typing unless you need it. So for the syntax above, all IS functions take one argument when typing them in a worksheet cell formula. What argument values can be used are discussed above and below this paragraph. Remember functions expect certain things in their argument lists, if you do not put the correct information in the list they will generate an error when run.
Since the IS functions are computer programs, they run when you press Enter to enter the formula that contains them. If any of the arguments are wrong, the function will return an error.
When typing the IS function in a worksheet cell formula, you need to replace the value argument. Some typical arguments you can use are:
|Argument Type||Cell Formula||Example Explanation|
|Cell Reference||=ISNUMBER( A1 )||Determines if the value in cell A1 is a number|
|Range Reference||=ISTEXT( A1:A10 )||Determines if the values in cell A1:A10 are text|
|Nested Function||=IF( ISERROR( A1/A2 ), 0, A1/A2 )||ISERROR used as a nested function to the IF function. Determines if the formula A1/A2 (division by 0 is always a problem) generates an error and if it does, IF outputs 0 controlling the error. If no error then IF outputs the calculated value of A1/A2|
|Formula||=ISREF( INDIRECT( "C1:C" & COUNTA( C:C )) )||Uses INDIRECT as a nested function to build a range reference, determines if INDIRECT generates a reference by returning TRUE|
|Cell and Range Names||=ISBLANK( Sales_2012 )||Determines if the cell named Sales_2012 is blank *|
* It is possible to name a cell or group of cells on a worksheet and use that name in place of a range reference or cell reference. Consult Excel help on how to name a cell.
The worksheet seen below contains some typical worksheet formulas that run the IS worksheet function. Pay close attention to the argument list and the syntax used to write the formula.
|2||11234AX||=IF( ISBLANK( A2 ), "" , VLOOKUP( A2, Parts_Table, 3, False )||Typical use of the ISBLANK function being used in an IF function to detect if someone has typed in cell A2. If they have, VLOOKUP runs. If not empty string is returned blanking the cell when not in use|
|4||1||=SUM( IF( NOT( ISERROR( A4:A7 ) ), A4:A7, FALSE ) )||Typical use of the ISERROR function being used to protect an array formula from erroring. ISERROR returns an array of TRUE/FALSE from range A4:A7. NOT flips the array from FALSE to TRUE and from TRUE to FALSE so all cells without errors register as TRUE. IF takes the TRUE/FALSE array and only returns the values from A4:A7 that have no errors. SUM then takes the values and adds them. This is an array formula so CTRL+Shift+Enter must be pressed when entering into a cell.|