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
comes before the
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.
Function Arguments dialog box for the IF function
amount to transfer to the
home account if the value
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.