Microsoft Office Tutorials and References

In Depth Information

**Using Operators in Formulas**

h
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

h
The next formula returns TRUE if the value in cell A1 does not equal the value in cell A2.

Otherwise, it returns FALSE:

=A1<>A2

h
Unlike some other spreadsheets (such as Lotus 1-2-3), Excel doesn’t have logical AND

and OR operators. Rather, you use functions to specify these types of logical 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

The goal is to subtract expenses from income and then multiply the result by the tax rate. But, if

you enter the preceding formula, you discover that Excel computes the wrong answer. The

formula multiplies expenses by the tax rate and then subtracts the result from the income. In other

words, Excel does not necessarily perform calculations from left to right (as you might expect).

The correct way to write this formula is

=(Income–Expenses)*TaxRate

To understand how this works, you need to be familiar with
operator precedence
— the set of

rules that Excel uses to perform its calculations. Table 2-3 lists Excel’s operator precedence.

Operations are performed in the order listed in the table. For example, multiplication is

performed before subtraction.