Microsoft Office Tutorials and References
In Depth Information
Chapter 11: Working with Formulas and Functions
EXCEL
chapter
11
Working with Formulas and Functions
Mathematical Operators
You can 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:
Operator
Operation
First
All operations enclosed in parentheses
+
Addition
Second
Exponential equations

Subtraction
Third
Multiplication and division
*
Multiplication
Fourth
Addition and subtraction
/
Division
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 — yielding
the wrong answer. The correct way to write the
formula is =(A2+B2+C2)/3. By enclosing the values in
parentheses, Excel performs the addition operations
before dividing the sum by 3.
%
Percentage
^
Exponentiation
=
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.
189