The 10 Excel Formulas That Will Make You Look Like a Genius – Number 3: IF
The IF formula must be one of the most versatile functions in Excel. I use it over and over when building analysis tools for clients. It is literally in nearly every formula I write. IF allows us to use True and False statements to drive different outcomes. The formula works on a basic “IF -> THEN -> ELSE” premise.
IF I remember my wife’s birthday, THEN I get to have a fun date night, ELSE I get to sleep on the couch
In Excel, this formula looks like:
=IF(I remember my wife’s birthday, I get a fun date night, I get to sleep on the couch)
You can see that the THEN and ELSE parts of the formula are not specifically stated but implied with the commas. So the three parts of the formula are If(True or False Statement, result if True, result if False).
Ready to see a simple example? In the dataset below I need to determine if new employees have completed all their paperwork and are ready to start work. They are able to start work if they have signed all of their new hire documents.
We can use an IF formula to see if their docs are signed.
=IF(D2=”Yes”,”OK”,”No”)
That was a fairly simple example. Let’s step it up a notch. We can actually nest an IF statement inside another IF statement to test if more than one statement is true.
Suppose this company does not allow employees under 21 to participate in the 401k program. That would exclude Jack from being able to participate. We also know that the IRS allows participants who are 55 years old and older to make a catch-up contribution of $6,500 per year. Let’s see if we can come up with a logical IF statement to cover these scenarios.
- If Age is less than 21, then 0
- If Age is greater than or equal to 55, then base plus $6,500
- Else, base rate of $20,500
Here is how that would look:
=IF(B2<21,0,IF(B2>=55,20500+6500,20500))
Notice that the formula ends with two parentheses “)).” This is because we used two formulas together. We always have to have the same number of opening parentheses and closing parentheses.
You can see that this formula has Jack with no contribution, while Andy and Kelly can both contribute up to $27,000.
There are as many ways to use the IF function as you can imagine. I use it to validate one dataset against another, to identify year to date and last year to date totals, and to rebalance portfolios of investments. I have used it to determine when an investment property is over or under valued and to dynamically roll dates in financial models. It truly is one of the most versatile functions in Excel.
To download the IF practice sheet, click the link below:
