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.