How to Use the Excel IF Function Like Batman

If you know how to use the Excel IF Function, you can easily write formulas that do such things as determine whether to offer someone a special discount, flag a series of test scores as Pass/Fail, or even override #N/A or #DIV/0 error messages. Once you’ve learned the basics of how to create a simple conditional statement using the Excel IF Function, you can also create logic trees by nesting the formulas. The IF Function is one of the simplest and easiest functions to learn, but is also one of the most flexible and powerful.

How the Excel IF Function Works

The Excel IF Function is actually fairly straight-forward and only consists of three arguments:

=IF(Conditional-Statement,Value-If-True,Value-If-False)

First, you create a conditional statement. This would be something to the effect of “If A2 is greater than 3″. Excel will then evaluate that statement as either TRUE or FALSE. If the statement is TRUE (e.g. if A2 = 5, then A2>3 will evaluate as TRUE), the function will return the second argument (Value-If-True) as the formula result. If the statement evaluates as FALSE (e.g. A2 = 1), then the function will return the third argument (Value-If-False) as the formula result. So, for the formula, =IF(A2>3,”A”,”B”), where A2 = 5, the formula will return “B”. If A2 = 1, the formula will return “A”.

Taking Your Excel IF Function to the Next Level

The real power of the Excel IF Function lies in its flexibility. In the example above, we used “A” and “B” as our Value-If-True and Value-If-False values. However, you can use nearly anything for those arguments (just be sure to put text in quotes). Instead of using “A” and “B”, we could have used “Over” and “Under” or “Seattle” and “Washington, DC” or simply used a number.

We can also use functions instead of text or number values. For example, we could say

=IF(A2>3,SUM(A2:A5),SUM(A6:A10))

Excel IF Function

=IF(A2=”Joker”,”I am the Knight”,”Bruce Wayne”)

So, in this situation, if A2 = 5, then the function will add all of the values in cells A2 through A5 together and report the sum. On the other hand, if A2 = 1, then it will add all of the values in cells A6 through A10 and report the sum. We can do this with nearly any function (this is called “nesting”). We can even use other IF functions as our arguments, as in

=IF(A2>3,IF(A=5,"A","C"),"B")

So, in this situation, if A2 = 5, then the formula will evaluate the first function (A2>3) as TRUE, which will cause it to evaluate the second argument (the IF function) as TRUE, which results in “A”. If A2 = 1, then the first function evaluates as FALSE, which directs Excel to skip over the second IF function to the Value-If-False argument, which is “B”.

Don’t Stop Here! Learn Even More!

While nesting functions may seem overwhelming at first, it doesn’t have to be. With additional study and practice, you’ll soon be creating complicated logic trees and evaluating complex conditional statements.

To learn more about formula nesting, sign up for our free newsletter. When you sign up, you’ll get free Excel tips and tricks directly in your inbox. You’ll also be the first to receive exclusive discounts on our training classes.

Posted in Excel and tagged , .

Leave a Reply

Your email address will not be published. Required fields are marked *