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
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%,.
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: