Microsoft Office Tutorials and References

In Depth Information

**Working with Logical Functions**

Figure 3-30

Projected monthly savings balances for the year

account balances at the end of the year

◗

6.
In cell L6, enter the formula
=N43
. The savings plan table at the top of the

worksheet displays 9,600, which is the year-end balance for the home account.

◗

7.
In cell L7, enter the formula
=L5+L6
to calculate the total savings amount from

both accounts at the end of the year. Under Diane’s current plan, the couple will

have $2,950 in the main savings account and $9,600 in the home savings account

at the end of the year.

Working with Logical Functions

Although Diane is pleased that $9,600 will be moved into the home savings account

in the next year, she’s concerned about the amount of money left in the main savings

account. Even more troubling are the month-to-month balances in that account. For

example, the balance in the main savings account will be only $375 at the end of August

and will remain well below $2,000 for several months of the year. Diane is concerned

that this savings plan will leave the couple with insufﬁ cient funds in the main savings

account to handle unforeseen expenses.

Part of the problem is that the couple’s net cash ﬂ ow is negative during several months

of the year. If they continue to transfer $800 into the home savings account during those

months, the main savings account might fall below an acceptable level. Diane wants to

modify her savings plan so that money is not transferred into the home savings account

unless the net cash ﬂ ow for that month is greater than or equal to $1,000. You need a

formula that can “choose” whether to transfer the funds. You can build this kind of

decisionmaking capability into a formula through the use of a logical function.

A logical function is a function that works with statements that are either true or false.

Consider a statement such as “cell A5 = 3”. If cell A5 is equal to 3, this statement has a

value of true; if cell A5 is not equal to 3, this statement has a value of false. Excel

supports many different logical functions, one of which is the IF function.

Using the IF Function

The IF function is a logical function that returns one value if a statement is true and

returns a different value if that statement is false. The syntax of the IF function is

IF(
logical_test,ƒ
value_if_true,
value_if_false
])