Posted by: tonyteaching | March 29, 2011

IF function and Nesting IF function

Simple IF (two conditions)

In Excel, the If function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

The syntax for the If function is:

If( condition, value_if_true, value_if_false )

condition is the value that you want to test.

value_if_true is the value that is returned if condition evaluates to TRUE.

value_if_false is the value that is return if condition evaluates to FALSE.

Example: Let’s pretend I have this data and on column D I wanna Excel automatically type “Good Sales” if Sales>$10,000.00 and “Bad Sales” otherwise

A B C D E
1
Sales Good Sales or Bad Sales
2 $20,000.00
3 $42,302.00
4 $53,001.00
5 $12,000.00
6 $ 2,050.00
7 $9,000.00
8 $40,000.00

Answer: To do that I type IF Formula in column D

A B C D E
1
Sales Good Sales or Bad Sales
2 $20,000.00 = IF (C2>10000, “Good Sales”, “Bad Sales”)
3 $42,302.00
4 $53,001.00
5 $12,000.00
6 $ 2,050.00
7 $9,000.00
8 $40,000.00

******************************************************

Nesting IF Function
source: http://personal-computer-tutor.com

Let’s use some new data. Open a blank workbook in Excel and enter this data:

A B C D E
1 Name District Sales Emp. Yrs Job Level
2 Linda East $20,000.00 2
3 Joe West $42,302.00 9
4 Bill East $53,001.00 3
5 Mary South $12,000.00 12
6 Mark South $ 2,050.00 6
7 John North $9,000.00 0
8 Ted East $40,000.00 4

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 AND have annual sales of more than $10,000 should be assigned a job level code of 2, and
all others should have a job level code of 1.

To include two criteria, when both criteria must be met, you must nest an AND function within your IF statement. Put this formula in cell E2:

=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.

************************************

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

=IF((AND(D2>5,C2>10000)),3,(IF((OR(D2>5,C2>10000)),2,1)))

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).

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:

=IF((B2=”East”),4,IF((B2=”West”),3,IF((B2=”North”),2,IF((B2=”South”),1,””))))

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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: