Microsoft Office Tutorials and References

In Depth Information

**Nested IF—Payroll Example**

FIGURE 8.9
The Actual IF Functions

’

Formulas Revealed

FIGURE 8.10
The Need for Nested IF Functions

—

More than One Decision

Here are the details of that request:

IF C2 contains a value of 90 or more, then you want to have an A in D2.

n

IF C2 is between 80 and 89, then you want a B in D2.

n

IF C2 is between 70 and 79, then you want a C in D2.

n

IF C2 is between 60 and 69, then you want a D in D2.

n

IF C2 is less than C, then put an F in D2.

n

To do so, you have to enter the following formula in cell B1:

¼

>
¼

“

”

>
¼

“

”

>
¼

“

”

>
¼

“

”

“

”

IF(C2

90,

A

,IF(C2

80,

B

,IF(C2

70,

C

,IF(C2

60,

D

,

F

))))

Figure 8.11 and Figure 8.12 show the results.

NESTED IF—PAYROLL EXAMPLE

Using the same payroll example, I can extend it to have a nested IF function as follows:

IF an employee worked up to 40 hours, I will pay him the regular rate, IF he worked

over 40 but less than 60, I will pay him1.5 times for the overtime, IF he worked over 60

hours, I should pay him 1 times for the overtime for the hours between 40 and 60, BUT

I will pay him 2 times the rate for the hour he worked over 60.

Figure 8.13 and 8.14 illustrate the above example. This example has two IF

conditions.