Nested IF Statements
In an earlier post, I covered IF statement. A limitation of an "IF statement" is that it only provides a solution if it satisfies ONE condition: yes or no? true or false?
As things are rarely black and white, what if there is more than one condition? How will the IF statement look? Is it even possible? The answer is “YES!” IF statements with more than one condition are called “Nested IF statements”. I’m going to show you three examples using Nested IF statements which hopefully you’ll be able to adapt to your scenario.
Before I begin, let’s recap the elements of an IF statement:
Excel Formula: =IF(logical_test, [value_if_true], [value_if_false])
*Remember, elements of the formula that is in square brackets are optional.
Formula Made Easy
Excel Formula: =IF(logical_test, [value_if_true],IF(logical_test_1, [value_if_true_1],IF(logical_test_2, [value_if_true_2],IF(logical_test_3, [value_if_true_3]...,[value_if_false]))))
Example 1: Categorical Grouping
This example applies if you need to group items into categories. I have five items, as seen below and I want to group them into two categories - fruit or vegetable.
Applying the IF formula into my scenario, it will look like this:
Formula Applied
=IF([@Item]="Apple","Fruit",IF([@Item]="Pear","Fruit",IF([@Item]="Mango","Fruit","Vegetable"))
A quick glance and that formula looks complicated. Let’s break it down into “simple English”:
First of all notice that there aren’t any cell references (e.g. A2, C5) in the formula. Why? Because my data is in a table so it uses the column name (Item) as a reference which is one of the many handy tools of putting your data in a table as if my data expands (i.e. I add more items, the formula will pull through).
Second, remember that an IF statement contains two results - a true and false and as my items are in Text format, I need to put “quotation” marks around it.
Lastly, when using Nested IF statements, build it up one at a time.
1. If [@Item] is "Apple" then "Fruit" otherwise "Vegetable" →
=IF([@Item]="Apple","Fruit","Vegetable")
2. If [@Item] is "Pear" then "Fruit" otherwise "Vegetable" → =IF([@Item]="Apple","Fruit",IF([@Item]="Pear","Fruit","Vegetable"))
As Apple and Pear are both NOT Vegetable, they share the same “value_if_false”. A crucial difference between (i) and (ii) is in (ii), there is an additional close brackets as there is effectively two IF statements.
3. If [@Item] is "Mango" then "Fruit" otherwise "Vegetable" → =IF([@Item]="Apple","Fruit",IF([@Item]="Pear","Fruit","IF([@Item]="Mango","Fruit",Vegetable")))
As Mango is also not a Vegetable, it shares the same “value_if_false” as Apple and Pear. And I’ll add another close bracket.
There it is!! Easy peasy!! If you do one IF statement at a time, it’s not that difficult.
Example 2: Grouping by Range
This example applies if you need to group by range, whether it’s age range or student grades, etc.
I have a list of student marks which I need to allocate a grade to them.
Once again let’s break it down into “simple English”:
1. If [@Mark] is less than 50 then it's a "Fail" otherwise "High Distinction" →
=IF([@Mark]<50,"Fail","High Distinction")
2. If [@Mark] is less than 50 it's a "Fail" but if it's less than 60 (greater than 50 but less than 60) then it's a "Pass" → =IF([@Mark]<50,"Fail",IF([@Mark]<60,"Pass","High Distinction"))
3. If [@Mark] is less than 50 it's a "Fail", if it's less than 60 (greater than 50 but less than 60) it's a "Pass" and if it's less than 70 (greater than 60 but less than 70) it's a "Credit", otherwise a "High Distinction" → =IF([@Mark]<50,"Fail",IF([@Mark]<60,"Pass","IF([@Mark]<70,"Credit","High Distinction")))
4. If [@Mark] is less than 50 it's a "Fail", if it's less than 60 (greater than 50 but less than 60) it's a "Pass" and if it's less than 70 (greater than 60 but less than 70) it's a "Credit", if it's less than 80 (greater than 70 but less than 80), it's a "Distinction", otherwise a "High Distinction" → =IF([@Mark]<50,"Fail",IF([@Mark]<60,"Pass","IF([@Mark]<70,"Credit",IF([@Mark]<80,"Distinction","High Distinction"))))
Reference:
Net in blog post cover photo was taken from 'Clipart Library': http://clipart-library.com/
Comments