IF-THEN Statements with Microsoft Excel

One of the basic statements for financial modeling is the IF-THEN function in Excel. While this may not be considered as robust as other approaches when doing actual computer programming (C++, Java, VB), in Excel it is rather straightforward and ubiquitous.

There are a couple of things to remember when using the IF-THEN approach:

• For basic results like "if this cell equals dog put a bone in the cell next to it" or "if the cell on my right equals rain display an umbrella sign in the cell below" or something like that. IF-THEN works well for binary situations, and is a method that the basic financial modeler can use and understand readily easily. The beauty of the function at its most basic level is the simplicity.

• IF-THEN can also be used for non-binary situations, meaning that one can "nest" several sub IF-THEN statements within the cell. For example, let's assume that you had four triggering events: apple, orange, grape and banana. Depending upon the word that was in a cell, there would be one of four results. This would look like (note that the extra spaces are in there for formatting purposes of this blog - you would not have spaces after the commas):

=IF(A1="Apple", "Watch for worm", IF(A1="Orange", "Citrus", IF(A1="Grape", "Soon to be a raisin", IF(A1="Banana", "Don't slip on the peel", "Do nothing")))).

You will notice that the last bit of this text includes "do nothing", which captures any text that is not one of the specified amounts you are seeking. If there were only four options (that somehow the input cell was blocked to prevent anything other than those four choices), the code would look like this:

=IF(A1="Apple", "Watch for worm", IF(A1="Orange", "Citrus", IF(A1="Grape", "Soon to be a raisin", "Don't slip on the peel"))).

By definition, only four choices will yield three IFs – think about the fourth IF as silent to help you see why this is the case.

• The financial modeler can also link IF-THEN statements to apply a mathematical operator to two or more statements in the cell. Thus, you could create a formula like =IF(A1="Pie", 1, 0) + IF(B1="Soup", 2, 0) + IF(C1="Water", 3, 0). You can do this with any operator and is useful if there are discrete components to the model you are developing. As you ultimately get further into advanced modeling, this can be used for triggers, or cells that create an influx of additional information when turned to a true state. Further discussion on that will occur as we move to intermediate level issues, but just remember that there is flexibility in the IF-THEN statement from this particular perspective.

• One shortcoming of the IF-THEN statement usage is that you can only have up to seven nested statements EVER in any function in Excel. For examples, the following is a maxed out version of the IF-THEN statement:

=IF(C5=1, 1, IF(C5=2, 2,IF(C5=3, 3, IF(C5=4, 4, IF(C5=5, 5, IF(C5=6, 6, IF(C5=7, 7, IF(C5=8, 8, "No")))))))) – if you try to add another nested IF statement, you will get an error message. Part of the rationale behind that is because evaluating the IF-THEN in the standard Excel environment (as well as computer language in general) takes some time to evaluate. Granted, it is not a lot of time when thinking about a machine calculating something faster than us, but each of the evaluation steps requires computing speed and memory. If you can imagine a nested 20-step IF-THEN statement in your mind and pretend that you must evaluate that statement in order until the last part of it, you will begin to see why there needs to be extra time for the computer to do analyze it. Thus, seven is the cutoff point, but in reality, you should never need that many nested IF-THEN statements anyway.

In summary, the IF-THEN statement can be a very useful, simple approach to binary and certain non-binary situations. It is not perfect for larger database inquiries or very large spreadsheets wherein the answer to the IF-THEN drives the model, but it can suffice for everyday simple financial modeling applications, and it easy to implement and interpret.