Tutorial Excel : Using Formula Operators

As mentioned earlier in this chapter, you can use symbols known as operators to define the operation your formula will accomplish. Some of these operators are mathematical operators that simply add, subtract, and multiply. Other operators allow you to perform more complex actions such as comparing values. For example, you can determine whether an employee has met his or her quota by using a comparison operator to see if actual sales are greater than or equal to a predetermined quota.
Table 1-1 lists the operators you can use in your Excel formulas.

Table 1-1: Operators for Excel Formulas

Operator	What It Does
+	The plus symbol adds two or more numeric values.
-	The hyphen symbol subtracts two or more numeric values.
/	The forward slash symbol divides two or more numeric values.
*	The asterisk symbol divides two or more numeric values.
%	The percent symbol indicates a numeric percent. Entering a percent sign after a whole number divides the number by 100 and formats the cell as a percentage.
&	The ampersand symbol is used to join or concatenate two or more textual values.
^	The carat symbol is used as an exponentiation operator.
=	The equal symbol is used to evaluate whether one value is equal to another value
>	The greater-than symbol is used to evaluate whether one value is greater than another value.
<	The less-than symbol is used to evaluate whether one value is less than another value.
>=	The greater-than symbol used in conjunction with the equal symbol evaluates whether one value is greater than or equal to another value.
<=	The less-than symbol used in conjunction with the equal symbol evaluates whether one value is less than or equal to another value.
<>	The less-than symbol used in conjunction with the greater-than symbol evaluates whether one value is not equal to another value.

Understanding the order of operator precedence

It’s important to understand that when you create a formula with several operators, Excel evaluates and performs the calculation in a specific order. For instance, Excel always performs multiplication before addition. This order is called the order of operator precedence. You can force Excel to override the built-in operator precedence by using parentheses to specify which operation to evaluate first.

Consider this basic example. The correct answer to (2+3)*4 is 20. However, if you leave off the parentheses, as in 2+3*4, Excel performs the calculation like this: 3*4 = 12 + 2 = 14. Excel’s default order of operator precedence mandates that Excel perform multiplication before addition. Entering 2+3*4 gives you the wrong answer. Because Excel evaluates and performs all calculations in parentheses first, placing 2+3 inside parentheses ensures the correct answer.
The order of operations for Excel is as follows:

• Evaluate items in parentheses.
• Evaluate ranges (:).
• Evaluate intersections (spaces).
• Evaluate unions (,).
• Perform negation (-).
• Convert percentages (%).
• Perform exponentiation (^).
• Perform multiplication (*) and division (/), which are of equal precedence.
• Perform addition (+) and subtraction (-), which are of equal precedence.
• Evaluate text operators (&).
• Perform comparisons (=, <>, <=, >=).

Operations that are equal in precedence are performed left to right.

Here is another widely demonstrated example. If you enter 10^2, which represents the exponent 10 to the 2nd power as a formula, Excel returns 100 as the answer. If you enter -10^2, you would expect -100 to be the result. Instead, Excel returns 100 yet again. The reason is that Excel performs negation before exponentiation, meaning that Excel is converting 10 to -10 before the exponentiation, effectively calculating -10*- 10, which indeed equals 100. Using parentheses in the formula -(10^2) ensures that Excel calculates the exponent before negating the answer, giving you -100.

Remembering the order of operations and using parentheses where appropriate will ensure that you avoid miscalculating your data.

Using nested parentheses

It’s a best practice to use parentheses whenever you can in formulas, even if the use of parentheses seem to be superfluous. The liberal use of parentheses can not only help you avoid calculation errors but also help you better understand what the formula is doing.

You can even nest parentheses in formulas. Nesting means putting parentheses inside of other parentheses. When a formula contains nested parentheses, Excel evaluates the most deeply nested operations first and works its way out. The following formula uses nested parentheses:

=((A1*B1)+(C1*D1))*E1

This formula has three sets of parentheses. Excel will evaluate the two nested sets of parentheses first, then will add those two results together. The added result will then be multiplied by the value in E1.

Note:
Every open parenthesis must have a matching close parenthesis. You can imagine that when you start adding lots of parentheses to your formula, determining which open parenthesis has a matching close parenthesis can get difficult. For its part, Excel offers some help by color coding the parentheses while you’re in Edit mode. Matching open and close parentheses will have the same color.

Categories: Formula