Excel Tip of the Week: IF

Today’s topic is an introduction to the IF function.

The IF function is great, but it sort of has a bad reputation because it has been abused so much. The trick to effectively using the IF function is to keep it simple. Today I will show you a very basic example and hopefully make the function more accessible.  Here are the required inputs of the IF function:

Here it is in English: IF(Test this condition, if the condition is TRUE give me this answer, if the condition is FALSE give me this answer)

 I say it every time, but an example will be the best way to learn this. Here we go:

1)      Download the example spreadsheet here.
2)      The scenario: We have customers and the number of sales calls each customer received before they bought from us. We would like to label each customer as “High-touch” or “Low-touch” based on number of sales calls [more than 5 is “High-touch”]
3)      Go to cell E3 and type in this formula: =IF(D3>$I$7,”High”,”Low”)
4)      Copy the formula down
5)      Good job

Several things to note here:
1) In English, here is what the formula is saying: If the number of sales calls is greater than 5, then give me the word “High”. Otherwise give me the word “Low”.

2) Notice how I didn’t “hardcode” the number 5 into the formula. I created a separate cell that could be changed in the future if need be. This is an Excel best-practice.

3) In the above example I used the “greater than” symbol. Here are the basic operators for use in an IF function (or any logical test):

4) This example contained only one level of logic. You might have heard the term “nested IF statements” which means there is more than one level of logic in the formula. Nested IF statements can get out of control really fast. They are the “abuses” I was referring to earlier in the post. My advice is to try to keep the number of IFs to a minimum, and to break your logic into more than one formula if need be.