Microsoft Office Tutorials and References

In Depth Information

**Creating Formulas**

Let’s consider the following formula: =C2+B8*4–

D10, and the values C2=50, B8=3, and D10=14.

Tip

Excel first takes B8*4, which is 12.

You do not have to create long,

complicated formulas to solve common

problems, such as adding up a long column

of numbers. Excel provides a

Next, since addition and subtraction are

both calculated at roughly the same time,

Excel moves left to right to complete its

calculations.

function

(built-in formula) just for that, and it’s

called SUM. You learn about functions

in Chapter 3, “Using Excel Functions.”

Thus, Excel takes C2 and adds the result

of B8*4, which is 50+12, or 62.

Finally, Excel takes this result and subtracts

D10, or 14, resulting in a final total: 48.

Considering the Order

of Operations

When you create compound formulas (formulas

that utilize more than one mathematical

operator), you need to stop and consider the order in

which Excel solves that formula. You see, Excel

doesn’t solve a formula by moving from the

left to the right, calculating as it goes. Nope,

Excel performs the operations in a formula in

a particular order:

If you don’t consider Excel’s order of operations,

you can run into problems with your formulas.

For example, suppose you are trying to find the

average sales amount per salesperson, using this

week’s sales totals. If you use a formula such

as =A2+B2+C2/3, you’ll get the wrong answer

because Excel will start by taking C2/3, and then

add that result to A2 and B2. To tell Excel to add

the sales totals first, use parentheses like this:

=(A2+B2+C2)/3. Excel performs the calculations

in parentheses first, and then it moves on to

multiplication/division and addition/subtraction.

Exponential operations and operations

within parentheses

Multiplication and division

Addition and subtraction

Order of Operations

When a formula uses both multiplication

and division, Excel decides which operation

to perform by moving left to right through

the formula.