Microsoft Office Tutorials and References

In Depth Information

**Let’s Be Logical**

The syntax of both AND and OR is to place the tests inside the function’s

parentheses; the tests themselves are separated by commas. Here is an

example that returns true if the value in cell D10 equals 20
or 30
or 40:

=OR(D10=20,D10=30,D10=40)

Check out how this works. In Figure 14-3, you see how you can use the

CHOOSE function to return the name of a month derived from the number of

the month. That works okay, but what if you type a wrong number or even a

non-numerical value as the first argument in CHOOSE?

As is, the CHOOSE function shown in Figure 14-3 returns the #VALUE! error if

the first argument is a number greater or less than the number of arguments

(not counting the first argument). So, as is, the function only works when the

first argument evaluates to a number between 1 and 12. If only life were that

perfect!

The next best thing, then, is to include a little validation in the function.

Think this through. Both statements must be true:

✓
The first argument must be greater than 0.

✓
The first argument must be less than 13.

The formula that uses CHOOSE needs an overhaul, and here it is, courtesy of

the AND function:

=IF(AND(B4>0,B4<13),CHOOSE(B4,”January”, “February”,

“March”, “April”, “May”, “June”, “July”,

“August”, “September”, “October”, “November”,

“December”),”That is not a month!”)

Wow, that’s a mouthful (or rather, a cell-full). The CHOOSE function is still

there, but it is nested inside an IF. The IF has a test (which is explained

shortly). If the test returns true, then the CHOOSE function returns the name

of the month. If the IF test returns false, then a simple That is not a

month! message is returned. Figure 14-5 shows this in action.

The test part of the IF function is this:

AND(B4>0,B4<13)

The AND returns true if the value in Cell B4 is both greater than 0
and
less

than 13. When that happens, the true part of the IF statement is taken, which

uses the CHOOSE statement to return a month name. Otherwise the “That

is not a month!” statement is displayed. In Figure 14-5 this is just what

happens in cells C9 and C15, which, respectively, look at the data values in

cells B9 and B15.