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:
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:
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:
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:
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!