IS Formulas in Microsoft Excel – Part II

This is continuation of a prior article covering 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 covers the functions ISEVEN, ISODD, ISLOGICAL and ISBLANK. These formulas, too, 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.

ISEVEN, ISODD

These are simple functions to determine whether or not a reference is an even number odd number. The form of the functions are =ISEVEN(value) or =ISODD(value) and the formulas return a TRUE statement if the value is even for ISEVEN or odd for ISODD. These are the types of formulas that could be used for storing products in a warehouse (like addresses, even on one side and odd on the other) or developing a spreadsheet to determine prime numbers (after number 2, since it is the only even prime number).

ISLOGICAL

This formula merely checks to see if the reference is TRUE or FALSE, and its form is =ISLOGICAL(value). At this point in the article, you have seen how these IS statements work, and this one is straightforward. If you are interested in taking a total tally of all of the TRUE or FALSE statements, you can use this formulas for each of the individual cells and then use =COUNTIF(Range_of_Data,FALSE) to count all of the FALSE references. That is one example of a scenario in which you might actually utilize in your own model.

ISBLANK

This formula checks the reference cell to see if there is any data in it, and its form is =ISBLANK(value). This is a formula that I have used many times and while a bit clunky on more complex spreadsheets, its simplicity makes it easy to incorporate. For example, when you are modeling anything with a waterfall structure, this formula comes in handy. You can create a series of formulas that will only kick in after a specific circumstance is reached. So, instead of having to manually adjust formulas across rows or columns, incorporating the ISBLANK language allows you to copy and past across the time horizon of your model. Let us say that you have a model where an investor has invested $10 in a company, and you want to calculate the return based on a specific sale date. Further, if a sale does not occur at the end of a period, some interim cash flow to an investor is paid. So, if the sale date is the variable, you may have a formula that looks to see if there was any activity in the prior cell to determine if any action should take place. Using the $10 investment, assume that you have years across the top and years in the column on the left. If the years across the top represent when cash flow will take place, the years on the left indicate when the sale of the investment occurs. Of course, of the year in the left column is greater then a year in the row (across the top), there would be no activity. This would be an example of when to use ISBLANK.

(As an aside, a waterfall is something that has a payoff structure that resembles water falling over a cliff. You would see this if you need to determine the returns to an investor over a period of years. If there was a sale of an investment in the first year, the investor might be getting only one payment – the initial sale. If the sale of the investment occurred in year 5, there may be smaller annual payments with a final larger payment at the end of year 5, thus the payoff stream would look like a waterfall when you model it. This will be explained in better detail in the financial articles to come.)

In summary, these simplistic formulas may not seem like terrific methods by which to establish your financial model, but a subset, particularly the ISBLANK function, can be very helpful in making a more complex situation easier to manage.