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:
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
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:
“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:
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.