Microsoft Office Tutorials and References

In Depth Information

**Working with Logical Functions**

Next, you’ll enter a formula to determine how much to transfer each month. For the

month of January, the formula to determine how much money is transferred is:

=IF(C33>=$L$11, $L$10, 0)

Cell C33 contains the net cash ﬂ ow for the month of January, cell L11 contains the

minimum net cash ﬂ ow needed before Diane will transfer the money between accounts,

and cell L10 contains the amount of money that Diane wants to transfer when the net

cash ﬂ ow meets or exceeds the minimum. For the current worksheet, this function tests

whether the net cash ﬂ ow for the month of January (cell C33) is greater than or equal to

1000 (cell L11). If it is, the formula returns 800 (cell L10) as the amount to transfer from

the main savings account into the home savings account. Otherwise, it returns 0 and no

money will be transferred that month.

This logical test uses both relative and absolute references. The C33 reference is

relative because you will copy this formula into cells for the remaining months of the year. The

$L$11 and $L$10 references are absolute so that the expression always references the

minimum net cash ﬂ ow speciﬁ ed in cell L11 and the amount to transfer speciﬁ ed in cell L10.

You’ll replace the formula in cell C39 with this IF function.

To insert the IF function to determine the monthly transfer amount:

◗

1.
Clear the contents of cell
C39
.

◗

2.
Click the
Formulas
tab on the Ribbon, if necessary.

◗

3.
In the Function Library group, click the
Logical
button, and then click
IF in the list

of logical functions. The Function Arguments dialog box for the IF function opens.

◗

4.
In the Logical_test box, enter
C33>=$L$11
. This tests whether the net cash flow

for January is greater than or equal to the value in cell L11 (1000).

When you type the IF

function directly in the

cell, remember that the

value_if_true
argument

comes before the

value_if_false
argument.

◗

5.
In the Value_if_true box, enter
$L$10
. If the value in cell C33 is greater than or

equal to the value in cell L11, the formula returns the value in cell L10 (800).

◗

6.
In the Value_if_false box, enter
. If the value in cell C33 is less than the value in
0

cell L11, the formula returns 0 and no money will be transferred from the main

savings account into the home savings account that month. See Figure 3-32.

Figure 3-32

Function Arguments dialog box for the IF function

amount to transfer to the

home account if the value

is true

test condition that is either true or false

value returned if the condition is true

no money is transferred if

the value is false

value returned if the condition is false

◗

7.
Click the
OK button. A value of 0 is displayed in cell C39. Because the net cash

flow for January is –2,060, no money will be transferred from the main savings

account into the home savings account that month.

◗

8.
Drag the fill handle for cell C39 over the range
D39:N39
to copy this formula with

the IF function into the remaining months of Diane’s proposed budget.