The IS functions

the IS functions

IS functions are used to test the type of value or reference found in a target cell. Each of these functions will check the type of value in a cell and, depending on the outcome, return a value of true or false. For example, the ISBLANK function returns the logical value true if the target value is a reference to an empty cell. If this is not the case, the function returns a logical false.

The IS functions are show in Table 1-1.

Suppose you want to use a worksheet to calculate the average of a range of cells, and you need to know if the range contains numbers before trying to calculate the average. If the AVERAGE function returns an error because it encountered blank cells, which IS function should we use to verify the existence of an error?

Since what we're really doing here is looking for an error, the ISERROR function is our best choice. Take a look at display Sheet 4 in your workbook and examine cell E4. Why does it display the words, 'Blank Range'? Why do the cells above give the correct answers for MAX and MIN scores?

The reason is that the formula in cell E4 references a range of values in column C, instead of column B, where the data actually resides. This produces an error in the AVERAGE formula, which is handled by the combined action of the ISERROR and IF functions. Correct the function so it gives the average of the contents in column B:

Next, add the word MEDIAN to your worksheet in cell E5. Do you remember the difference between an arithmetic mean (average) and a median? An average adds up all the numbers in a set and divides that sum by the number of members in the set. When the number of members in a set is even, the average returns a value with a decimal remainder, representing the halfway point in the data. When a set contains an odd number of members, the whole number value at the halfway point will be the median. Let's say you want to run a calculation on a set using the MEDIAN function, and the number of members in a set is odd. How can you do this using the IF and IS functions? Of course, you will also have to use the COUNT function to determine the number of members in the set.

To use ISODD and ISEVEN, you must activate the Analysis ToolPak. Select Tools > Add-Ins and check Analysis ToolPak. If ToolPak isn't listed as an option, run the Excel Setup program and install it.

Figure 1-6: The Add-Ins dialog box

The formula used to choose between MEDIAN and AVERAGE in cell E5 is shown below. Of course, this example is a bit contrived; you probably wouldn't ever use this kind of logic for MEDIAN and AVERAGE calculations. It is, however, a good demonstration of how you can add decision-making abilities to your Excel formulas. Soon, the formulas you have written will be deciding, all by themselves, which formula to run in a given circumstance.

You can even make the label in cell D5 smart as follows:

Build these formulas yourself on Sheet 4 in the workbook and see how they work. If you remove the last name and score on the list, how will the formulas react?

IS functions are best used in formulas for testing an outcome of a calculation. Frequently, they are combined with the IF function to provide a graceful method of error handling on the worksheet.

notes about IS functions

Value arguments are not converted. For example, in most other functions where a number is required, Excel functions will convert '16' to the value 16. However, in the formula ISNUMBER('16'), '16' is not converted from its text value, and ISNUMBER returns false.