IF statements are a powerful feature that can allow you to get more calculated with fewer calculations. Even if you are already familiar with IF statements, we will show you some advanced techniques you may not be aware of.
The format is: =IF(logical test, action if true, action if false)
Here is an example (you may want to try it first with the IF exercise, where we calculate sales commission based on volume:
The formula is =IF(C3>=200000,0.06,0.04)
Rather than having to manually put in commission rates, by using the IF statement, you can automate the process.
Note that this is a very simple commission structure, If it were more involved, such as many levels of rates, we would be better off using a lookup table, something that we will cover in a future lesson.
Suppose, we had multiple criteria? What if there was a kicker in the rate, for example, if a salesperson had been there more than one year? For this you can use what is called a nested IF statement, which is an IF statement within an IF statement.
In this case, we build a further test within the true and false conditions for the years on board. The formula we created is =IF(C2>=200000,IF(B2>1,0.08,0.06),IF(B2>1,0.06,0.04)).
result if true: IF(B2>1,0.08,0.06) Here we do a test within a test and put in the commission kicker
result if false: IF(B2>1,0.06,0.04) Here we do a similar test (note lower commission rate)
We could have done an alternate version, where we make the first test be the years and then test for the commission within each year. Either way works. We show both in the exercise file.
Nested IF statements can be taken to several levels (if within an if within an if, etc.). However, if there are too many "nests", then formulas can get complicated to fix or for the next person to understand. You may need to rethink your layout.
Here are some other ways to use IF statements:
Quality Control. If statements can be used to tell you if something is out of bounds. For example, it could be a check on input in a long list. You could see if any values inputted are too high or too low. We will show examples in a future lesson.
Building a Database. You can use IF statements to help you build fields in a database. In the above example, you might have a database of salespeople. You might have a field that flags high achievers. You could use an IF statement to help build this. =IF(C2>500000. "Y", "")
IF statements are powerful ways to streamline or automate parts of your work or build in quality checks. Take advantage of them.
Next we will cover some other functions you can use with IF statements to make them even more powerful and user friendly.