|
Excel IF Statements, Part II: Nesting Functionsby Linda Johnson, MOS
In the last issue of Techtrax, I taught you how to do simple IF statements based on one condition and one value if true or false. I promised I would show you how to do this with multiple conditions and multiple true/false values, so that’s what I’m going to do now. For this, let’s use some new data. Open a blank workbook in Excel and enter this data:
Let’s pretend this is data which shows your salespeople’s district,
annual sales, and number of years employed by your company. Now, let’s
suppose you want a formula in column E that will assign a job level
based on two different criteria: Salespeople who have been employed for
more than 5 years =IF((AND(D2>5,C2>10000)),2,1)
Then, grab cell E2 by its fill handle and drag it down to cell E8 to
“relatively” copy it to the rest of the cells in column E.
You see this formula uses a nested AND function for its “logical test”. The AND function will decide if both conditions are true. Remember, the IF function’s second and third arguments return a “value if true” and a “value if false”, so if the AND function returns a “true”, a 2 will be placed in the cell and if the AND function returns a “false”, a 1 will be placed in the cell. Excel will look into the deepest nested function first, then work it’s way out, so the first thing it does is determine the result of the AND function, then it goes out to the IF function and performs that, based on the results of the AND function. This formula, if entered correctly, should show job level code 2 for Joe and Mary only, because they are the only two who have worked for the company more than five years AND had annual sales greater than $10,000. Similarly, you can use a nested OR function if you want a job level code of 2 to be applied if an employee meets EITHER criterion, instead of both criteria. A nested OR function works the same way as an AND function, except it will return the 2, if ANY of the criteria are met, instead of requiring that ALL criteria are met. Put this one in E2 now and copy it down to E8: =IF((OR(D2>5,C2>10000)),2,1)
In this case, everyone is assigned a job level 2 except John,
because he is the only one who did not meet either criterion, since
he has not worked for the company for more than 5 years OR had
annual sales greater than $10,000.
=IF((AND(D2>5,C2>10000)),3,(IF((OR(D2>5,C2>10000)),2,1)))You can use more than two criteria in nested ANDs and ORs. Simply separate them by commas. These examples work great when there are only two job levels, but what if there are more than two? In this case, you will have more than two criteria and more than one value if true and value if false. Let’s say we want to assign a job level 3 if the employee meets BOTH criteria above, a job level 2 if the employee meets EITHER criteria above, and a job level 1 if the employee doesn’t meet any of the criteria above. Put this formula in E2 and copy it down to E8
Notice what we are doing here is basically nesting one IF statement
within another. However, though we have two different logical tests
and two different values if true, we only have one value if false.
You can nest up to 7 IFs in one formula, so that means you can
return up to 8 different results (7 different values if true and one
value if false).
=IF((B2="East"),4,IF((B2="West"),3,IF((B2="North"),2,IF((B2="South"),1,""))))You can also nest multiple IFs if you want
different results based on different values in the same cell. For
this example, let’s say all of the employees in the East district
will be assigned a job level 4, the West ones will be job level 3,
the North ones will be job level 2, and the ones in the South will
be job level 1. Our formula would look like this:
Note that the value if false is “”, which tells Excel to leave the
cell empty if no match is found in column B for that row. If you
delete the contents of any cell in column B or change it to anything
other than East, West, North, or South, you will see the
corresponding cell in column E would now be empty. Remember, you
must have something in your third argument (value if false) or Excel
will simply enter the text “FALSE” into that cell if it does not
find a match. I think I’ve given you enough information now, so you can build pretty extravagant IF statements. Just remember that limit of seven nested IFs though, because there is no way to increase this using an IF statement. However, when you really need more than eight conditions, there are other functions that will serve you better than Nested IFs. One set of functions are the LOOKUP functions and I will cover them in a later article. Are we having fun yet? I am, and I hope you are too. Linda |
|
.
