Microsoft Office Tutorials and References

In Depth Information

**Logical Operators**

Logical Operators

Logical Operators

So far, you’ve seen the basic arithmetic operators (which are used for addition,

subtraction, division, and so on) and the cell reference operators (used to specify one

or more cells). There’s one final category of operators that’s useful when creating

formulas:
logical operators
.

Logical operators let you build conditions into your formulas so the formulas

produce different values depending on the value of the data they encounter. You can use

a condition with cell references or literal values.

For example, the condition A2=A4 is true if cell A2 contains the same value as cell

A4. On the other hand, if these cells contain different values (say 2 and 3), then the

formula generates a false value. Using conditions is a stepping-stone to using

conditional logic. Conditional logic lets you perform different calculations based on

different scenarios.

For example, you can use conditional logic to see how large an order is, and provide

a discount if the total order cost’s over $5,000. Excel
evaluates
the condition,

meaning it determines if the condition’s true or false. You can then tell Excel what to do,

based on that evaluation.

Table 17-3 lists all the logical operators you can use to build formulas.

Table 17-3.
Logical operators

Operator

Name

Example

Result

=

Equal to

1=2

FALSE

>

Greater than

1>2

FALSE

<

Less than

1<2

TRUE

>=

Greater than or equal to

1>=1

TRUE

<=

Less than or equal to

1<=1

TRUE

<>

Not equal to

1<>1

FALSE

You can use logical operators to build standalone formulas, but that’s not

particularly useful. For example, here’s a formula that tests whether cell A1 contains the

number 3:

=(A2=3)

The parentheses aren’t actually required, but they make the formula a little bit clearer,

emphasizing the fact that Excel evaluates the condition first, and then displays the

result in the cell. If you type this formula into the cell, then you see either the

uppercase word TRUE or FALSE, depending on the content in cell A2.

On their own, logical operators don’t accomplish much. However, they really shine

when you start combining them with other functions to build conditional logic. For

example, you can use the SUMIF() function, which totals the value of certain rows,