Microsoft Office Tutorials and References

In Depth Information

**Choosing the Right Value**

If this test is true, then Warm! is returned. If the test is false, then nothing is

returned.

A few key points about this triple-level IF statement:

✓
The IF that tests whether the number of elapsed days is 30 or fewer has

a value to return if true (HOT!) and a value to return for false (whatever

is returned by the next nested IF).

✓
The outer IF and the innermost IF return nothing when their test is false.

✓
On the surface, the test for 60 or fewer days
also would catch a date

that is 30 days or fewer since the last order date!
This is not really what

is meant to be. The test should be whether the number of elapsed days

is 60 or fewer
but more than 30!
You do not have to actually spell it out

this way because the formula got to the point of testing for the 60-day

threshold only because the 30-day threshold already failed. Gotta watch

out for these things!

Choosing the Right Value

The CHOOSE function is ideal for converting a value into a
literal.
In

plainspeak, this means turning a number, such as 4, into a word, such as April.

CHOOSE takes up to 30 arguments. The first argument acts as key to the rest

of the arguments. In fact, the other arguments do not get processed, per se,

by the function. Instead, the function looks at the value of the first argument

and, based on that value, returns one of its other arguments.

The first argument must be, or evaluate to, a number. This number, in turn,

indicates which of the following arguments to return. For example, the

following returns Two: =CHOOSE(2, “One”, “Two”, “Three”).

The first argument is the number 2. This means that the function will return the

second in the list of arguments following the first argument. But watch out —

this is not the same as returning the second argument! It means to return the

second argument
not counting the first one.

Figure 14-3 shows a useful example of CHOOSE. Say you have a column of

months that are in the numerical form (1 through 12). You need to have these

displayed as the month names (January through December). CHOOSE to the

rescue!