Already on ArticleSlash?

Forgot your password? Sign Up

IF THEN Statements With Microsoft Excel

 


Visitors: 4,006

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:

=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.

Russ Steward has more than fifteen years of experience in investment banking and private equity, and has developed hundreds of financial models and analyses in Excel. For more information, please visit http://www.makefinancialmodels.blogspot.com

(740)

Article Source:


 
Rate this Article: 
 
Reformat Column Widths in Microsoft Excel
Rated 4 / 5
based on 5 votes
ArticleSlash

Related Articles:

IS Formulas in Microsoft Excel - Part I

by: Russ Steward (September 09, 2008) 
(Computers and Technology/Software)

MATCH Function in Microsoft Excel

by: Russ Steward (August 16, 2008) 
(Computers and Technology/Software)

LOOKUP Function in Microsoft Excel

by: Russ Steward (July 20, 2008) 
(Computers and Technology/Software)

IS Formulas in Microsoft Excel - Part II

by: Russ Steward (September 09, 2008) 
(Computers and Technology/Software)

Microsoft Excel How to Create Charts

by: Adrian Sallis (June 24, 2008) 
(Computers and Technology/Software)

Microsoft Excel Training - Not Just for Newbies Anymore

by: Christine Harrell (July 25, 2007) 
(Computers and Technology)

How and Why to Convert Microsoft 2007 Word/Excel/PPT to PDF?

by: Sarina Lee (November 15, 2011) 
(Computers and Technology/Software)

VLOOKUP and HLOOKUP Functions in Microsoft Excel

by: Russ Steward (July 17, 2008) 
(Computers and Technology/Software)

Time Value Formulas in Microsoft Excel - Part I

by: Russ Steward (September 25, 2008) 
(Computers and Technology/Software)

Reformat Column Widths in Microsoft Excel

by: Michael C Podlesny (July 28, 2008) 
(Computers and Technology/Software)