Microsoft Office Tutorials and References

In Depth Information

• The next formula returns TRUE if the value in cell A1 is less than the value in cell A2. Otherwise, it returns

FALSE:

=A1<A2

Logical comparison operators also work with text. If A1 contains Alpha, and A2 contains Gamma, the for-

mula returns TRUE because Alpha comes before Gamma in alphabetical order.

• The following formula returns TRUE if the value in cell A1 is less than or equal to the value in cell A2.

Otherwise, it returns FALSE:

=A1<=A2

• The next formula returns TRUE if the value in cell A1 does not equal the value in cell A2. Otherwise, it re-

turns FALSE:

=A1<>A2

• Excel doesn't have logical AND and OR operators. Rather, you use functions to specify these types of logic-

al operators. For example, this formula returns TRUE if cell A1 contains either 100 or 1000:

=OR(A1=100,A1=1000)

This last formula returns TRUE only if both cell A1 and cell A2 contain values less than 100:

=AND(A1<100,A2<100)

Operator precedence

You can (and should) use parentheses in your formulas to control the order in which the calculations occur. As

an example, consider the following formula that uses references to named cells:

=Income–Expenses*TaxRate

Subtraction or negation?

One operator that can cause confusion is the minus sign (–), which you use for subtraction. However, a minus

sign can also be a negation operator, which indicates a negative number.

Consider this formula:

=–^2

Excel returns the value 9 (not –9). The minus sign serves as a negation operator and has a higher precedence

than all other operators. The formula is evaluated as “negative 3, squared.” Using parentheses clarifies it:

=(–)^2

The formula is
not
evaluated like this:

=–(3^2)