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.
Search JabSto ::

Custom Search