Microsoft Office Tutorials and References

In Depth Information

**Working Out the Order of Operations in a Formula**

You could also write

=7/SUM(A12:A68)

This would divide 7 by the sum of all the values in cells A12:A68.

And that’s just for starters. When you begin to see and appreciate how these various

parts
can
interact, you’ll be making a leap in your Excel understanding.

NOTE:
Remember, formulas are subject to automatic recalculation, so any change in the values

contributing to the formula will immediately change the formula’s result.

Working Out the Order of Operations in a Formula

Once you start writing formulas, you need to keep something else in mind—something

you may not have thought about since high school. Ask yourself, what result does this

formula deliver?

=32+4/3

Hmmm. You could be adding 32 and 4 and then dividing that result by 3, yielding 12—or

are you starting with 32 and simply adding 4/3 to it?

That sort of ambiguity leads us to Excel’s order of operations—a priority listing of which

sort of mathematical operation is carried out first when a formula encounters the kind of

mixed message just shown. Here’s the listing, arrayed in order of priority (don’t worry, I’ll

explain):

1.

Parentheses

2.

Exponentiation

3.

Multiplication

4.

Division

5.

Addition/subtraction

Consider this formula:

=(54+6)*3-2

Here, Excel takes the two values between the parentheses, 54 and 6, and adds them,

yielding 60. It then multiplies that result by 3, and
then
subtracts 2 from 180, winding up

with 178. Finally, 60 times 3 equals 180, and 180 minus 2 yields 178. The order of

operations is confirmed:

1.

Treat the values within parentheses as a unit.

2.

Then give priority to multiplication, and then to subtraction.