Using Operators in Formulas

As previously discussed, an operator is the basic element of a formula. An operator is a symbol that represents an operation. Table 2-1 shows the Excel-supported operators.

Table 2-1: EXCEL-SUPPORTED OPERATORS
Open table as spreadsheet

Symbol	Operator
+	Addition
–	Subtraction
/	Division
*	Multiplication
%	Percent[*]
&	Text concatenation
∘	Exponentiation
=	Logical comparison (equal to)
>	Logical comparison (greater than)
<	Logical comparison (less than)
>=	Logical comparison (greater than or equal to)
<=	Logical comparison (less than or equal to)
<>	Logical comparison (not equal to)
[*]Percent isn't really an operator, but it functions similarly to one in Excel. Entering a percent sign after a number divides the number by 100 and formats the cell as percent.

Reference Operators

Excel supports another class of operators known as reference operators; see Table 2-2. Reference operators, described in the following list, work with cell references.

Table 2-2: REFERENCE OPERATORS
Open table as spreadsheet

Symbol	Operator
: (colon)	Range. Produces one reference to all the cells between two references.
, (comma)	Union. Combines multiple cell or range references into one reference.
(single space)	Intersection. Produces one reference to cells common to two references.

Sample Formulas That Use Operators

These examples of formulas use various operators:

• The following formula joins (concatenates) the two literal text strings (each enclosed in quotes) to produce a new text string: Part-23A:

   ="Part-"&"23A" 

• The next formula concatenates the contents of cell A1 with cell A2:

   =A1&A2 

  Usually, concatenation is used with text, but concatenation works with values as well. For example, if cell A1 contains 123 and cell A2 contains 456, the preceding formula would return the value 123456. Note that, technically, the result is a text string. However, if you used this string in a mathematical formula, Excel will treat it as a number. Many Excel functions will ignore this "number" because they are designed to ignore text.

• The following formula uses the exponentiation operator to raise 6 to the third power, to produce a result of 216:

   =6^3 

• A more useful form of the preceding formula uses a cell reference instead of the literal value. Note this example that raises the value in cell A1 to the third power:

   =A1^3 

• This formula returns the cube root of 216 (which is 6):

   =216^(1/3) 

• 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 formula 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 returns FALSE:

   =A1<>A2 

• Unlike some other spreadsheets (such as 1-2-3), Excel doesn't have logical AND or 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. If you enter the preceding formula, you discover that Excel computes the wrong answer. Rather, 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.

Table 2-3: OPERATOR PRECEDENCE IN EXCEL FORMULAS
Open table as spreadsheet

Symbol	Operator
Colon (:), comma (,), space( )	Reference operators
−	Negation
%	Percent
∘	Exponentiation
* and /	Multiplication and division
+ and −	Addition and subtraction
&	Text concatenation
=, <, >, <=, >=, and <>	Comparison

Use parentheses to override Excel's built-in order of precedence. Returning to the previous example, the formula without parentheses is evaluated using Excel's standard operator precedence. Because multiplication has a higher precedence, the Expense cell multiplies by the TaxRate cell. Then, this result is subtracted from Income-producing an incorrect calculation.

The correct formula uses parentheses to control the order of operations. Expressions within parentheses always get evaluated first. In this case, Expenses is subtracted from Income, and the result multiplies by TaxRate.

Nested Parentheses

You can also nest parentheses in formulas-that is, put parentheses inside of parentheses. When a formula contains nested parentheses, Excel evaluates the most deeply nested expressions first and works its way out. The following example of a formula uses nested parentheses.

 =((B2*C2)+(B3*C3)+(B4*C4))*B6 

This formula has four sets of parentheses. Three sets are nested inside the fourth set. Excel evaluates each nested set of parentheses and then sums the three results. This sum is then multiplied by the value in B6.

It's a good idea to make liberal use of parentheses in your formulas even when they aren't necessary. Using parentheses clarifies the order of operations and makes the formula easier to read. For example, if you want to add 1 to the product of two cells, the following formula will do the job:

 =A1*A2+1 

Because of Excel's operator precedence rules, the multiplication will be performed before the addition. Therefore, parentheses are not necessary. You may find it much clearer, however, to use the following formula even though it contains superfluous parentheses:

 =(A1*A2)+1 

Tip 

Every left parenthesis, of course, must have a matching right parenthesis. If you have many levels of nested parentheses, you might find it difficult to keep them straight. Fortunately, Excel lends a hand in helping you match parentheses. Matching parentheses are colored the same although the colors can be difficult to distinguish if you have a lot of parentheses. Also, when the cursor moves over a parenthesis, Excel momentarily displays the parenthesis and its matching parenthesis in bold. This lasts for less than a second, so watch carefully.

In some cases, if your formula contains mismatched parentheses, Excel may propose a correction to your formula. Figure 2-2 shows an example of Excel's AutoCorrect feature in action.

Figure 2-2: Excel's Formula AutoCorrect feature often suggests a correction to an erroneous formula.

Caution 

Simply accepting the correction proposed in the dialog box is tempting, but be careful. In many cases, the proposed formula, although syntactically correct, isn't the formula that you want. I omitted the closing parentheses after January. In Figure 2-2, Excel proposed this correction:

 =SUM(January/SUM(Total)) 

In fact, the correct formula is

 =SUM(January)/SUM(Total) 

Don't Hard-Code Values

When you create a formula, think twice before using a literal value in the formula. For example, if your formula calculates 7.5 percent sales tax, you may be tempted to enter a formula such as

 =A1*.075 

A better approach is to insert the sales tax rate into a cell and use the cell reference in place of the literal value. This makes it easier to modify and maintain your worksheet. For example, if the sales tax range changes to 7.75 percent, you need to modify every formula that uses the old value. If the tax rate is stored in a cell, you simply change one cell and all the formulas automatically get updated.