Microsoft Office Tutorials and References

In Depth Information

**Session 1.1**

Sample Excel formulas using arithmetic operators

Figure 1-12

Operation

Operator

Example

Description

Addition

+

=10+A5

Adds 10 to the value in cell A5

=B1+B2+B3

Adds the values of cells B1, B2, and B3

Subtraction

–

=C9–B2

Subtracts the value in cell B2 from the

value in cell C9

=1–D2

Subtracts the value in cell D2 from 1

Multiplication

*

=C9*B9

Multiplies the value in cell C9 by the value in

cell B9

=E5*0.06

Multiplies the value in cell E5 by 0.06

Division

/

=C9/B9

Divides the value in cell C9 by the value in

cell B9

=D15/12

Divides the value in cell D15 by 12

Exponentiation

^

=B5^3

Raises the value in cell B5 to the third power

=3^B5

Raises 3 to the power specified in cell B5

Entering a Formula

Reference Window

•

Click the cell where you want the formula result to appear.

•

Type = and then type the expression that calculates the value you want.

•

For a formula that includes cell references, such as B2 or D78, type the cell reference, or

use the mouse or arrow keys to select each cell.

•

When the formula is complete, press the Enter key (or press the Tab key or click the

Enter button on the Formula bar).

If an expression contains more than one arithmetic operator, Excel performs the calcu-

lation in the order of precedence. The
order of precedence
is a set of predefined rules

that Excel follows to calculate a formula by determining which operator is applied first,

which operator is applied second, and so forth. First, Excel performs exponentiation (^).

Second, Excel performs multiplication (*) or division (/). Third, Excel performs addition (+)

or subtraction (–).

For example, because multiplication has precedence over addition, the formula

=3+4*5
results in the value
23
. If the expression contains two or more operators with the

same level of precedence, Excel applies them from left to right in the expression. In the

formula
=4*10/8
, Excel first multiplies
4
by
10
and then divides the result by
8
to produce

the value
5
.

When building a formula, you must add parentheses to change the order of operations.

Excel will calculate any expression contained within the parentheses before any other part

of the formula. The formula
=(3+4)*5
first calculates the value of
3+4
and then multiplies

the total by
5
, resulting in the value
35
. (Note that without the parentheses, Excel would

produce a value of
23
, as noted in the previous paragraph.) Figure 1-13 shows other

examples of Excel formulas using the order of precedence rules.