IS Formulas in Microsoft Excel – Part I

Excel offers several similar formulas within the Information category that allows a modeler to extract TRUE or FALSE information, based on the design of the model. This article will briefly describe the various functions, including: ISERR, ISERROR, ISNA, ISNONTEXT, ISNUMBER, ISREF, and ISTEXT. These formulas can play a big role in simple financial models and I think that many individuals who are normally familiar with Excel do not know how to incorporate them in a way that may make the modeling process easier.

ISERR, ISERROR

These two formulas will check to see if the reference is an answer or an error. The form for each is simply =ISERR(value) or =ISERROR(value) (Note that in each of these the form includes a "value" component, which should be thought of as the cell reference you are targeting). The only difference between these two formulas is that ISERROR can include the error of #N/A, and if the reference is #N/A, the result of TRUE is returned. For ISERR, the value of FALSE is returned for #N/A because it does not recognize that particular error. For that reason, I use the ISERROR only. This formula can come in handy when evaluating a formula designed to lookup data in an area (like VLOOKUP or HLOOKUP). It can act as the MATCH formula in this capacity, treating an error as a non-match and thereby allowing the modeler to have a means by which to check if the required data is actually contained in the data range. For example, if you have a list of inventory products and you need to know the amount sold on a specific day, you could do the following to get the exact amount or return an error message:

=IF(ISERROR(VLOOKUP(Product,Range,Number,False)),"No Data", VLOOKUP(Product,Range,Number,False)).

ISNONTEXT, ISTEXT, ISNUMBER

These functions are fairly straightforward, with one determining if a reference is text, number or not text at all. The short of this section is that ISNONTEXT and ISTEXT are opposite, not surprisingly, and ISNONTEXT and ISNUMBER are the same EXCEPT FOR a blank cell, which ISNONTEXT states TRUE and ISNUMBER states FALSE. There is no real mystery in these formulas, whose form takes the place of =ISNONTEXT(value), =ISTEXT(value) or =ISNUMBER(value). You could use these to check the names of employees in a long list, addresses in a database or some combination that requires confirmation of text status.

ISNA, ISREF

These two formulas are designed to specifically determine is there is an #N/A or #REF! in a reference cell. The form is either =ISNA(value) or =ISREF(value). I have not found a good time to use either of these formulas, and you may never find a good time either. Having said that, if you have a long list of data and want to do a quick copy and paste of a simple formula to find the bad data, one of these two Excel formulas could do the trick. Let us say that you have 4,000 entries of products sold last year and you need to know how much was sold each day of the week. If you were checking to see if any results were #N/A (indicating no sales on that particular day of the week), you could use =ISNA(Range_of_Friday_Sales) for each cell next to the product to see if it is truly #N/A. Frankly, that is cumbersome and I doubt you would ever use something like that, but Excel has made the formula available to use, so if you are in a bind, use it. The same would apply for use with ISREF, but instead of finding #N/A, you would be searching for bad formulas. Let us say that you have a model with a lot of data in a row and you begin to delete some of those rows. Of each row was dependent upon the next to provide the right answer, you will end up generating a #REF! That is when you can use ISREF, but once again, I cannot see a real life scenario where the ISREF will trump another formula you create in your financial model.

Additional Information formulas are covered in the next article entitled "IS Formulas in Microsoft Excel – Part II."