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.

lists all the logical operators you can use to build formulas.

Table 7-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, depending on whether the row matches a set condition. Or you can use the IF( ) function to determine what calculation you should perform.

The IF( ) function has the following function description:

        IF(condition, [value_if_true], [value_if_false])

In English, this line of code translates to: If the condition is true, display the second argument in the cell; if the condition is false, display the third argument.

Consider this formula:

        =IF(A1=B2, "These numbers are equal", "These numbers are not equal")

This formula tests if the value in cell A1 equals the value in cell B2. If this is true, you'll see the message "These numbers are equal" displayed in the cell. Otherwise, you'll see "These numbers are not equal."

---

Note: If you see a quotation mark in a formula, it's because that formula uses text. You must surround all literal text values with quotation marks. (Numbers are different: You can enter them directly into a formula.)

---

People often use the IF( ) function to prevent Excel from performing a calculation if some of the data is missing. Consider the following formula:

        =A1/A2

This formula causes a divide-by-zero error if A2 contains a 0 value. Excel then displays an error code in the cell. To prevent this from occurring, you can replace this formula with the conditional formula shown here:

        =IF(A2=0, 0, A1/A2)

This formula checks if cell A2 is empty or contains a 0. If so, the condition is true, and the formula simply gives you a 0. If it isn't, the condition is false, and Excel performs the calculation A1/A2