Excel 2007

Complex formulas

Watch the video! (12:26 min)

Download the example to work along with the video.

Complex formulas defined

Simple formulas have one mathematical operation. Complex formulas involve more than one mathematical operation.

Simple formula: =2+2
Complex formula: =2+2*8

To calculate complex formulas correctly, you must perform certain operations before others. This is defined in the order of operations.

The order of operations

The order of mathematical operations is important. If you enter a formula that contains several operations, Excel knows to work those operations in a specific order. The order of operations is:

1. Operations enclosed in parenthesis
2. Exponential calculations (to the power of)
3. Multiplication and division, whichever comes first
4. Addition and subtraction, whichever comes first

A mnemonic that can help you remember this is Please Excuse My Dear Aunt Sally (P.E.M.D.A.S).

Example 1

Using this order, let's see how the formula 20/(8-4)*8-2 is calculated in the following breakdown:

Example 2

3+3*2=?

Is the answer 12 or 9? Well, if you calculated in the order in which the numbers appear, 3+3*2, you'd get the wrong answer: 12. You must follow the order of operations to get the correct answer.

To calculate the correct answer:

1. Calculate 3*2 first because multiplication comes before addition in the order of operations. The answer is 6.
2. Add the answer obtained in step 1, which is 6, to the number 3 that opened the equation. In other words, add 3 + 6.
3. The answer is 9.