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