Microsoft Office Tutorials and References
In Depth Information
Understanding logical functions
Figure 14-1 You can create complex conditional tests using the AND function.
You’ll find the And Or Not.xlsx file with the other examples on the companion website.
The OR function returns the logical value TRUE if any one of the conditional tests is true;
the AND function returns the logical value TRUE only if all the conditional tests are true.
Because the NOT function negates a condition, you usually use it with other functions. NOT
instructs Excel to return the logical value TRUE if the argument is false or the logical value
FALSE if the argument is true. For example, the formula =IF(NOT(A1=2), "Go", " ") tells Excel
to return the text Go if the value of cell A1 is anything but 2 .
Nested IF functions
Sometimes you can’t resolve a logical problem using only logical operators and the AND,
OR, and NOT functions. In these cases, you can nest IF functions to create a hierarchy of
tests. For example, the formula =IF(A1=100, "Always", IF(AND(A1>=80, A1<100), "Usually",
IF(AND(A1>=60, A1<80), "Sometimes", "Who cares?"))) states, in plain language, the
following: If the value is 100 , return Always ; if the value is from 80 through 99 , return Usually ;
if the value is from 60 through 79 , return Sometimes ; or if none of these conditions is true,
return Who cares? . You can create formulas containing up to 64 levels of nested functions.
Other uses for conditional functions
You can use all the conditional functions described in this section as stand-alone formulas.
Although you usually use functions such as AND, OR, NOT, ISERROR, ISNA, and ISREF within
an IF function, you can also use formulas, such as =AND(A1>A2, A2<A3), to perform simple
conditional tests. This formula returns the logical value TRUE if the value in A1 is greater
than the value in A2 and the value in A2 is less than the value in A3. You might use this type
of formula to assign TRUE and FALSE values to a range of numeric database cells and then
use the TRUE and FALSE conditions as selection criteria for printing a specialized report.