Microsoft Office Tutorials and References
In Depth Information
Testing on One Condition
Figure 14-2:
Looking
for hot
inventory
items.
Okay, take a breath. I leave no Excel user behind!
The outer IF tests if the inventory in column D is equal to or less than half
(50 percent) of the reorder level. The piece of the formula that does that is
=IF(D9<=(E9×0.5). This test, of course, produces a true or false answer. If
it is false, then the false part of the outer IF is taken (which is just an empty
string found at the end of the formula: ,””)).
That leaves the whole middle part to wade through. Stay with it!
If the first test is true, then the true part of the outer IF is taken. It just so
happens that this true part is another IF function:
IF(NOW()-C9<=30,”HOT!”,IF(NOW()-C9<=60,”Warm!”,””))
The first argument of the inner IF tests whether the number of days since the
last order date (in column C) is less than or equal to 30. You do this by
subtracting the last order date from today, as obtained from the NOW function.
If the test is true, and the last order date is within the last 30 days, then HOT!
is returned. A hot seller indeed! If the test is false then . . . wait, what’s this?
Another IF function! Yes: an IF inside an IF inside an IF. If the number of days
since the last order date is greater than 30, then the next nested IF tests
whether the number of days is within the last 60 days:
IF(NOW()-C9<=60
Search JabSto ::

Custom Search