Microsoft Office Tutorials and References

In Depth Information

**Chapter 8: IF Functions**

þ
0

Figure 8.4 reveals all the formulas, using CTRL

(left apostrophe or accent mark

earlier) or CTRL

(tilde). The right side shows the results.

In Figure 8.5, I show how to use the IF function to calculate regular time and

overtime in a weekly payroll. The overtime rate is different than the regular working

hours

þ
B

salaries are calculated the following way: IF an employee

worked up to 40 hours a week, the salary

’

rate. Employees

’

Rate

Hours worked
, otherwise I

=

×

calculate his salary for the first 40 hours to be
Rate

40
, plus one and half times the

×

rate (Rate

1.5) for the hours worked over 40 (Hours worked

40).

×

>

In cell E3 I have the following function:

IF(D3

40, C3*D3, 40*C3

(D3

–

40)*1.5*C3)

=

<=

+

The IF function in cell E3 has three parts:

1.
The logical test is
D3

40
: Regular time only?

2.
The value if true:
C3*D3
: Regular time only. Rate

<=

Hours worked.

×

3.
The value if

false:
40*C3

(D3

–

40)*1.5*C3
.40

Rate

þ

Overtime

+

×

×

1.5

Rate.

×

See Figure 8.6.

FIGURE 8.5
Using the IF Function to Calculate Overtime

FIGURE 8.6
The IF Function for Overtime