03 IF…
I’m tempted to get heady and try quoting Rudyard Kipling or an old Greek dude like Socrates or Aristotle, but then you’ll probably just read somebody else’s blog, because after all you’re only interested in Excel’s IF…
Anyway, this post will refer to “logic tests” throughout. If you need a refresher check out our Simple Logic post.
IF
Let’s start with the syntax:
=IF( logical test , what to do if true , what to do if false )
At the most basic level, IF is easy: you perform a logic test, and then you tell Excel what to do if the test is TRUE and what to do if the test is FALSE. Its even easier if you follow ExcelMates method:
- Set up the problem. Take a minute to think how you can test the problem. Look for phrases like “do [something] if…” or “do this when this is [true/false]…” Another helpful clue is when you see “condition” or something similar like “conditional”, “conditionality”, “based on”, “criteria”, “according to”, etc. Finally, “if” is naturally a great clue for a logic test!
- Map it out with a decision tree. This will help identify if your logic is sound (this is especially important when we start using nested IFs)
- Determine how you want the result to be presented. For example, it could be a message (“Too High”) or a number (10% or $100 or 35).
- Put it into Excel =IF(TEST,TRUE,FALSE)
Let’s look at a couple of examples:
(Example 1) You have two shipping rates depending on the weight of a package. If the package weighs less than 10kg the shipping cost is $5; if it weighs more than 10kg the shipping cost is $7.50.
- First step: what are we really asking here? We can pick out two conditions from the problem. The first is that we charge $5 if the package weighs more than 10kg. The second is that we charge $7.50 if the package weighs 10kg or more. That’s a logic test: “does the package weigh more or less than 10kg?” or in Excel terms: [weight]>=10
- Next: Diagram it out
- In this case, the best way to present the data is with the resulting shipping cost.
- Put it into Excel: =IF([weight]>=10,7.5,5)
If you want to see this in Excel click on the tab labeled “Example 1″ in IF examples
Note: you could have easily flipped the equality in the test by saying: [weight] < 10. Neither way is necessarily better, whichever you prefer.
(Example 2) An employee in a members-only store is trying to calculate the price that various customers should pay based on their membership type (either Basic or Premium). Basic members receive a 10% discount, and Premium members receive a 20% discount.
- What is the customer’s membership type? Here we can say “is the member basic?” because we know that alternatively they must be premium.
- Diagram:
- We want to return the final price, not the discount. So for Basic, that would be original price multiplied by 90%. For Premium it would be original price multiplied by 80%
- Put it in Excel:
If you want to see this in Excel click on the tab labeled “Example 2″ in IF examples (the file also has a detailed breakdown of an extra example on the tab “Example 3″)
Next steps:
In these basic IF statements you only have 1 test and 2 results (TRUE, FALSE). You might be wondering what you would do if you need multiple tests (if the package weighs less than 10, between 10 and 20 and greater than 20, for example). Check out ExcelMates’ coverage of Nested IFs!
