More of the Same in Excel 2010 Part 4
Again, project this scenario onto 500 or more students, and you’ll appreciate how swiftly VLOOKUP can deliver information about any one of them. And before we move on, you’ll want to know that VLOOKUP has a sibling named HLOOKUP, which works in precisely the same way, except its lookup table runs horizontally, e.g., (Figure 3 – 39):
Figure 3–39. A horizontal lookup table, for use with HLOOKUP If the table above has been written in say, E13:O24, an HLOOKUP might look like this:
HLOOKUP(D3,E13:O14,2,TRUE) Here a value in cell D3 is looked up in table E13:O14—and the tax percentage—the “answer”—is culled from the second row, not column.
If: Worth Knowing—No Ifs, Ands, or Buts On to another function, one no less valuable—IF. As its name suggests, IF provides a way to sift between (at least) two data alternatives , and to act upon each accordingly. Again, that abstract introduction needs to be exemplified.
OK. Say I want to be able to award a bonus of $250 to any member of my sales team who exceeds $10,000 in sales in a given month. And suppose I start with this collection of data in cells A5:B8 (Figure 3– 40):
Again, the size of the sales team doesn’t really matter—we’re just trying to prove the point.
In cell C5—Ted’s row—I could write: =IF(B5>10000,250,0)
And there’s your first IF statement. As with VLOOKUP’s default, IF requires three arguments:
What’s called a logical test—a condition which, if met, makes one thing happen, and if it isn’t met, makes something else happen. In our case, the logical test is B5>10000 (note the greater than symbol) and it means, in effect: if the number in cell B5 exceeds 10,000, then… Value if true. That is, what’s going to happen If the condition is met. Again here, if B5 surpasses 10000, the value 250 will be posted in C5—the cell in which I’ve written the IF statement.
Value if false. What’s going to happen if the condition is not met. Here, if the number in B5 falls below 10,000, a zero will be posted in C5—no bonus.
And we can copy this original formula down the C colunn for as many salespersons as we need— and no dollar signs, this time,—because we’re assessing a different sales total for each salesperson. We’ll see here, of course, that Natalie and George are in line for the $250.
And our Value if true/false consequences can be textual. For example, I could write our statement to read like this:
=IF(B5>10000,”Well Done!”,”We’ll Get ‘Em Next Month”)
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!
But through the magic of automatic recalcuation, feel free to type in different numbers in any and all of the three cells—B12, C12, or D12. If you can nab a 4.9% rate, you’ll pay $1,061,45 instead—37 bucks less a month. Don’t spend it all in one place.
In Conclusion…
If you want to do more with your workbooks than compile data into lists, you need to know at least a bit about how to move your data to the next level—by writing formulas and utilizing Excel’s numerous functions, and making something with the information that wasn’t there before. In large measure, that’s what spreadsheets are about.
As usual, these skills take practice—but again, the more you know about Excel’s formula-writing capabilities, the more you can get the data to do what you want them to do, and to tell you what you need to know. And now that we’ve gotten that message across, let’s take a look at the ways in which you can get your spreadsheet message across—by learning how to format your data.