The IF function in Microsoft Excel is one of the most important, versatile, and powerful tools excel has to offer and a must have for anyone becoming an excel pro. This function allows you to build decisions and logic into your sheets, which you can leverage in tons of ways.
Theory behind how it works (with examples):
For example; let’s say you need to calculate commissions for a sales executive and the sales commission is based on the incremental revenue brought into the business for each deal closed, like in the table below:
You could use an IF Statement to tell excel what to do in different scenarios. In our case, we’ll use IF to determine what the commission rate is going to be for each of this executive’s individual sales.
You just saved a ton of time! Instead of going through the logic manually like most do (which would have taken you a while) you used an excel tactic to complete this task in seconds!
You can use an IF statement to add any kind of logic into your spreadsheet that you need. What if your company decides that they will pay a higher rate of commission for any deals closed within a region that has historically been difficult to sell in? Or a more common scenario; a last minute sales contest has been rolled out; any deals closed on Mondays or Fridays, within a three week period are paid at double the rate. No sweat; IF Statements handle this as easily as cutting warm butter!
The IF Statement in the example used criteria that were manually entered, but you can make things more efficient by referring to other cells as well. You could just point the references to a table in another sheet or workbook.
But wait; this is just the tip of the iceberg though. Excel pros don’t unlock the true power of IF until they begin nesting it with other functions, or even other IF statements. I’ll tell you more, but first lets break down the IF Function in detail.
The Syntax
IF(Logic Test, Value if True, Value if False)
Logical Test – This is where you let excel know what you’re evaluating. Is the sale over $8,000? Does the employee live out of state? The result of the logical test must be either yes (true) or no (false).
Value if True – Here you enter what you want excel to execute if the result of the logical test is true. What do you want excel to do if the sale is over $8,000? Or if the employee indeed lives out state? You could have Excel return some text by entering the text in quotes (ie. “Lives Out of State”). Or, you could ask excel to execute another function by entering it here. This is called nesting; we’ll cover this in more detail a bit later.
Value if False – Last thing here is to tell excel what to do If the logical test is false. Again, You could enter another function or you could have the IF Statement return some text by entering it in quotes (ie. “Does Not Live Out of State”).
Nesting
Intermediate excel users know which functions to use in different situations through practice, practice, and more practice. This skill alone will get you far. However, truly advanced excel users have unlocked the power of nesting which will take anything you create in excel to the next level, immediately! The only way to get skilled at nesting is, again, through practice.
Almost anything you want to accomplish in excel can be done through nesting different functions together, like a jigsaw puzzle. Lets get into it…
How Nesting Works
Your company is pushing Product A and introduced a higher commission rate for sales reps that are able to sell it (and no commissions for Product B, its being discontinued). Here is what it looks like:
IF Statements are basically a true or false logic test, all you need to do is tell excel to execute another IF Statement whenever the function returns FALSE (or TRUE). If the second IF returns FALSE, tell it to execute another IF, and keep nesting until you have what you need.
More Nesting with AND/OR Functions
Continuing with our example; your company decided to make your life a little more painful by introducing a multi-tiered commission schedule, where the higher the sale the higher the commission will be for each individual deal. Here is the table:
In order to accomplish this, we’ll need to use a couple of IF’s sidekicks; AND and OR. If you needed to use multiple logical tests in a single IF Statement (ie. Is the number is greater than 2,000 AND less than 4,000) then you would need to use one of these functions. Lets take a look at how it applies to our example:
The AND function will return a value of TRUE (comes in handy inside of an IF Statement) only if all logical tests within the function are true. In order to use AND you would need to insert it into the IF function like this:
=IF(AND(Logical Test 1, Logical Test 2… and so on), If TRUE, If FALSE)
In our case we are checking whether J4 (3,814) is both greater than 2,000 AND less than 4,000. The result returns 15%. If it isn’t, you should tell excel to return the words “Keep Nesting” as a temporary placeholder. Lets chain a few of these together and see what happens.
Pro Tip: When I was first learning to nest functions together I would try to write out the entire formula in one cell on my first try. That’s like going to a Vegas casino and expecting to hit the jackpot on your first play; it doesn’t really work. I realized that it gets increasingly difficult to spot errors as I nest more functions together. I decided it was more effective to break my formulas out into different cells, confirm each one worked the way I wanted, then copy and paste them together into the super-nested formula. This alone has saved me TONS of headaches.
You can use multiple IF statements by nesting them together and go multiple levels deep in order to build super-dynamic templates and calculations that factor in different scenarios. Practice this; once you master nesting you will already be levels above your co-workers and on your way to impressing your boss.
