Microsoft Office Tutorials and References
In Depth Information
Testing on One Condition
As seen in the previous example, a common use of IF is to see how two values
compare to each other and return either one value or the other, depending
on how you set up the test in the first argument.
IF is often used as a validation check to avoid errors. For example, suppose
you have a financial worksheet that uses a variable percentage in its
calculations. The user must enter this percentage each day, but it must never be
greater than 10 percent. To avoid the chance of errors, you could use the
IF function to display an error message in the adjacent cell if you
mistakenly enter a value outside the permitted range. Assuming the percentage is
entered in cell A3, here’s the required IF function:
=IF(A3>.1, “ERROR: the % in A3 IS TOO LARGE”, “”)
Figure 14-1 shows how IF can be put to good use in a business application.
A fictitious store shop — Ken’s Guitars (kinda snappy, don’t you think?) —
keeps tabs on inventory in an Excel worksheet.
an eye on
Column D shows the inventory levels, and Column E shows the reorder
levels. It works this way: When a product’s inventory level is the same or
less than the reorder level, it is time to order more of the product. (I don’t
know about you, but I love the thought of being surrounded by a bunch of
Stratoblasters!) The cells in column F contain a formula.
The formula in cell F8 is =IF(D8<=E8,”ORDER”,””). It says that if the
number of Stratoblaster 9000 guitars in stock is the same or less than the
reorder level, then return Order. If the number in stock is greater than the
reorder level, then nothing is returned. Nothing is returned because three are