Microsoft Office Tutorials and References
In Depth Information
If: Worth Knowing—No Ifs, Ands, or Buts
=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):
Figure 3–40. Sales data, to be analyzed with IF
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