Microsoft Office Tutorials and References
In Depth Information
Testing on One Condition
Logically, this decision looks like this:
If the restaurant is Italian, then
If the restaurant serves manicotti, then
we will have manicotti
else
we will have pizza
This looks a lot like programming code. I have left out the End If statements
on purpose to avoid confusion because the IF function has no equivalent value.
That’s it! Make note that the inner IF statement has a result for both the true
and false possibilities. The outer IF does not. Here is the structure of this as
nested Excel IF statements: =IF(Restaurant=Italian, IF(Restaurant
serves manicotti, “manicotti”, “pizza”), “”). If the restaurant
were not Italian, then it wouldn’t matter what we ate (as indicated by the
third argument of the outer IF being empty).
You can nest up to 64 IF statements, although things are likely to get very
complicated once you go beyond 4 or 5.
You can apply a nested IF statement to increase the sophistication of the
inventory worksheet from Figure 14-1. Figure 14-2 has an additional column —
Hot Item. A Hot Item can take three forms:
If the inventory level is half or less of the reorder level and the last sale
date is within the last 30 days, then this is a Hot Item . The point of view
is that in 30 days or less the stock sold down to half or less than the
reorder level. This means the inventory is turning over at a fast pace.
If the inventory level is half or less of the reorder level and the last sale
date is within the last 31–60 days, then this is a Warm Item . The point of
view is that in 31–60 days the stock sold down to half or less than the
reorder level. This means the inventory is turning over at a medium pace.
If neither of the preceding two conditions is met, the item is not
assigned any special status.
There are Hot Items and there are Warm Items. They both must meet the
common criterion that the inventory is 50 percent or less of the reorder level.
Only after this first condition is met does the second criterion — the number
of days since the last order — come into play. Sounds like a nested IF to me!
Here is the formula in cell G9:
=IF(D9<=(E9×0.5),IF(NOW()-C9<=30,”HOT!”,IF(NOW()C9<=60,”Warm!”,””)),””).

Search JabSto ::

Custom Search