The IF function allows you to make a logical comparison between a value and what you expect by testing for a condition and returning a result if True or False.
- =IF(Something is True, then do something, otherwise do something else)
So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.
IF statements are incredibly robust, and form the basis of many spreadsheet models, but they are also the root cause of many spreadsheet issues. Ideally, an IF statement should apply to minimal conditions, such as Male/Female, Yes/No/Maybe, to name a few, but sometimes you might need to evaluate more complex scenarios that require nesting* more than 3 IF functions together.
* “Nesting” refers to the practice of joining multiple functions together in one formula.
Remarks
While Excel will allow you to nest up to 64 different IF functions, it’s not at all advisable to do so. Why?
- Multiple IF statements require a great deal of thought to build correctly and make sure that their logic can calculate correctly through each condition all the way to the end. If you don’t nest your formula 100% accurately, then it might work 75% of the time, but return unexpected results 25% of the time. Unfortunately, the odds of you catching the 25% are slim.
- Multiple IF statements can become incredibly difficult to maintain, especially when you come back some time later and try to figure out what you, or worse someone else, was trying to do.
If you find yourself with an IF statement that just seems to keep growing with no end in sight, it’s time to put down the mouse and rethink your strategy.
Let’s look at how to properly create a complex nested IF statement using multiple IFs, and when to recognize that it’s time to use another tool in your Excel arsenal.
Examples
Following is an example of a relatively standard nested IF statement to convert student test scores to their letter grade equivalent.
Additional examples
Following is a very common example of calculating Sales Commission based on levels of Revenue achievement.
Post navigation