Creating Complex Formulas

In Excel complex formulas can include several operations. Use complex formulas in Excel to calculate more extensive data.

Complex formulas

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:

Formula 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.