Page 285 - Excel Data Analysis

16 537547 AppD.qxd 3/4/03 11:55 AM Page 271

FORMULA BASICS IN EXCEL D

EXCEL OPERATORS (CONTINUED)

Reference Operators
You specify a range of cells for your formula with one of three different operators. See Chapter 1 for more
information on selecting cell ranges. The following table describes each of the operators:

OPERATOR SYMBOL PURPOSE EXAMPLE
Range : Specifies a range of cells from the cell A1:A15 specifies all cells from A1 to A15.
reference before the operator to the cell
reference after the operator.
Union , Combines multiple ranges in one reference. =SUM(A1:A10, C1:C10) sums cells in A1
through A10, and C1 through C10.
Intersection (space) References the cells that are common (A1:C10 B1:D10) specifies the common
between two ranges. cells in the range A1:C10 and B1:D10. In the
formula =SUM(A1:C10 B1:D10), Excel
sums only the values in cells B1 through C10.

OPERATOR PRECEDENCE

When Excel evaluates a formula that contains operators, that the following formula gives a result of 49 (7 raised
it uses a specific order to determine which part of the to the second power): =(3+4)^2.
formula it evaluates first. This is known as the
precedence order. For example, the following formula The following table shows the precedence order,
has a result of 19 because Excel calculates 4^2 first and highest to lowest, that Excel uses to evaluate operators
then adds 16 to 3: =3+4^2. To change the order of in formulas. If the operators in the formula all have the
precedence, you can place parentheses around 3+4 so same order of precedence, Excel evaluates the equation
from left to right.

PRECEDENCE OPERATORS SYMBOL
1 Parentheses ( )
2 Reference operators : , (space)
3 Minus Sign - (negates a number before any calculations)
4 Percent sign %
5 Multiplication and Divison * /
6 Addition and Subtraction + -
7 Concatenation &
8 Comparison operators = < > <= >= <>

271