Microsoft Office Tutorials and References

In Depth Information

**Nesting IF Functions**

•
3.25 or higher: 3% raise

•
2.5 or higher: 1% raise

•
Under 2.5: no raise

You can build the IF statement by following these steps:

11. Test for the highest condition first. Excel stops testing when the first

condition is met. If the first test checks to see whether an employee

had a rating of higher than 2.5, then anyone from 2.5 to 5 receives a 1%

raise. In this case, you want to give a 5% raise to anyone with a rating

of 4.5 or greater. Therefore, the formula starts out as

=IF(D2>=4.5,5%,.

22. There is only one argument left in the current IF function
—
the argu-

ment for value_if_false. Instead of using a value as the third argu-

ment, start a second IF function to be used if the first test is FALSE.

This IF function starts out IF(D2>=4,4.5%,. Combine this start of an

IF function with the first IF function: =IF(D2>=4.5,5%,IF(D2>=4,4.5%,.

Caution

These IF formulas are hard to read. There is a temptation to use them

for situations with very long lists of conditions. Whereas Excel 2003

prevented you from nesting more than seven levels of IF functions,

Excel 2007 and later allow you to nest up to 64 IF statements. Before

you start nesting that many IF statements, you should consider using

VLOOKUP, which is explained later in this chapter.

33. There are still three possible raise levels and only one argument

left in the second IF function. Start a third IF function to be used as

the value_if_falseargument for the second IF function:

IF(D2>=3.25,3%,. At this point, if the employee did not rank above 3.25,

only two possibilities are left. The employee is either 2.5 and above

for a 1% raise, or he or she gets no raise.

44. Create the fourth IF function: IF(D2>=2.5,1%,0).

55. With the four IF functions, be careful to provide four closing paren-

theses at the end of the function:

=IF(D2>=4.5,5%,IF(D2>=4,4.5%,IF(D2>=3.25,3%,IF(D2>=2.5,1%,0%)))) (see

Figure 12.6
)
.