Microsoft Office Tutorials and References

In Depth Information

**Excel’s Order of Operations**

Creating a Basic

Formula

multiplication (*), division (/), and exponentiation (^). Table 17-1 lists these

ingredients, also known as
arithmetic operators
.

•
Numbers
. These ingredients are known as constants or
literal values
, because

they never change (unless you edit the formula).

•
Cell references
. These references point to another cell, or a range of cells, that

you need data from in order to perform a calculation. For example, say you have

a list of 10 numbers. A formula in the cell beneath this list may refer to all 10 of

the cells above it in order to calculate their average.

•
Functions
. Functions are specialized formulas built into Excel that let you

perform a wide range of calculations. For example, Excel provides dedicated

functions that calculate sums and averages, standard deviations, yields, cosines

and tangents, and much more.

•
Spaces
. Excel ignores these. However, you can use them to make a formula

easier to read. For example, you can write the formula
=3*5 + 6*2
instead of

=3*5+6*2
. (The only exception to this rule applies to cell ranges, where spaces

have a special meaning. You’ll see this described on page 473.)

Table 17-1.
Excel’s arithmetic operators

Operator

Name

Example

Result

+

Addition

=1+1

2

−

Subtraction

=1−1

0

*

Multiplication

=2*2

4

/

Division

=4/2

2

^

Exponentiation

=2^3

8

%

Percent

=20%

0.20

Note:
The percentage (%) operator divides a number by 100.

Excel’s Order of Operations

For computer programs and human beings alike, one of the basic challenges when it

comes to reading and calculating formula results is figuring out the
order of

operations
—mathematician-speak for deciding which calculations to perform first when

there’s more than one calculation in a formula. For example, given the formula:

=10 - 8 * 7

the result, depending on your order of operations, is either 14 or -46. Fortunately,

Excel abides by the standard rules for order of operations, meaning it doesn’t

necessarily process your formulas from left to right. Instead, it evaluates complex formulas

piece-by-piece in this order: