Microsoft Office Tutorials and References

In Depth Information

**If: Worth Knowing—No Ifs, Ands, or Buts**

Written this way, one of these declarations will appear in the cells for each salesperson, once it’s

copied for all. I think they’d prefer the $250, but be that as it may, note that
textual
if true/false

consequences require
quotes
around them.

And nothing stops you from incorporating other functions into IF, as long as you remember to keep

your parentheses in line. Let’s get back to Alice, and her 83 test average:

=IF(AVERAGE(D10:H10)>85,”Honor Roll”,”Nice Try”)

If Alice’s average were to exceed 85, Honor Roll appears in whatever cell the statement is written. In

this case we see that AVERAGE is used here to establish the logical test—and once you’ve become

practiced with nesting functions inside other ones, such as the example we’re studying here, you can

really start to rock ‘n’roll. The data possibilities multiply exponentially.

There’s one more function we can squeeze into into this sampler, and this one has real-world

pertinence—
PMT
—short for Payment—a financial formula that can easily tell you how much money

you can expect to pay for a mortgage—like it or not.

Stripped to its essentials, PMT has three arguments:

=PMT(rate,nper,pv)

Rate
stands for your annual rate, one that will need to be
divided
by 12, or whatever the payment

interval (example coming shortly).
Nper
signifies the number of payments you need to make across the

life of the mortgage, and
Pv denotes the present value of the mortgage.

Here’s that example: you want to take out a 30-year, $200,000 mortgage at an interest rate of 5.2%.

Let’s enter these three values in cells B12, C12, and D12, shown in Figure 3–41:

(Again, we haven’t formatted these values.)

Figure 3–41.
The basic three elements needed to write PMT: interest rate, number of payments, and current

value of the loan

The .052 is, after all, 5.2%, and the 360 represents 360 monthly payments over 30 years. In E12, type:

=PMT(B12/12,C12,D12)

Note again: it’s not obvious, but you need to divide the interest rate by 12 if you pay monthly, as we

see above. (Were you to pay semi-monthly you’d have written B12/6—but you’d also be making 180

payments instead, and would have to enter that revised estimate in C12.)

When the smoke clears you should see this in E12:

$1,098.22

You’ll note of course that Excel here has
automatically
formatted our result—by imparting currency

features to the figure, as well as daubing the numbers red. Why red? To indicate that you’re
debiting
your

account whenever you incur this monthly charge.

Then you can go ahead and write
this
in any cell you choose:

=E12*C12

That little formula multiplies the monthly debit by 360, the number of times you’ll actually have to

pay out. Result: $395,359.83, for a $200,000 mortgage at 5.2%. Ouch!