Microsoft Office Tutorials and References
In Depth Information
Chapter 11: Working with Formulas and Functions
Working with Formulas and Functions
You use mathematical operators in Excel to build
formulas. Basic operators include the following:
Excel performs operations from left to right, but
gives some operators precedence over others,
following the rules you learned in high school math:
All operations enclosed in parentheses
Multiplication and division
Addition and subtraction
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.
Less than or equal to
Greater than or equal to
Not equal to
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).
Range operator. Evaluates the reference as a single reference,
including all of the cells in the range from both corners of the reference.
Union operator. Evaluates the two references as a single reference.
Intersect operator. Evaluates the cells common to both references.
Intersect operator. Evaluates the intersecting cell or cells of the
column labeled Totals and the row labeled Sales.