Microsoft Office Tutorials and References
In Depth Information
Chapter 11: Working with Formulas and Functions
11
CHAPTER
EXCEL
Working with Formulas and Functions
Mathematical Operators
You use mathematical operators in Excel to build
formulas. Basic operators include the following:
Operator Precedence
Excel performs operations from left to right, but
gives some operators precedence over others,
following the rules you learned in high school math:
Operator
Operation
Order
Operation
+
Addition
First
All operations enclosed in parentheses

Subtraction
Second
Exponential operations
*
Multiplication
Third
Multiplication and division
/
Division
Fourth
Addition and subtraction
%
Percentage
^
Exponentiation
When you are creating equations, the order of
operations determines the results. For example,
suppose you want to determine the average of
values in cells A2, B2, and C2. If you enter the
equation =A2+B2+C2/3, Excel first divides the
value in cell C2 by 3, and then adds that result to
A2+B2 — producing the wrong answer. The correct
way to write the formula is =(A2+B2+C2)/3. By
enclosing the values in parentheses, you are telling
Excel to perform the addition operations in the
parentheses before dividing the sum by 3.
=
Equal to
<
Less than
<=
Less than or equal to
>
Greater than
>=
Greater than or equal to
<>
Not equal to
Reference Operators
You can use Excel’s reference operators to control how a formula groups cells and ranges to perform
calculations. For example, if your formula needs to include the cell range D2:D10 and cell E10, you can
instruct Excel to evaluate all the data contained in these cells using a reference operator. Your formula
might look like this: =SUM(D2:D10,E10).
Operator
Example
Operation
:
=SUM(D3:E12)
Range operator. Evaluates the reference as a single reference,
including all of the cells in the range from both corners of the reference.
,
=SUM(D3:E12,F3)
Union operator. Evaluates the two references as a single reference.
[space]
=SUM(D3:D20 D10:E15)
Intersect operator. Evaluates the cells common to both references.
[space]
=SUM(Totals Sales)
Intersect operator. Evaluates the intersecting cell or cells of the
column labeled Totals and the row labeled Sales.
195