Creating Complex Formulas

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

Complex formulas defined

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

The order of mathematical operations is important. If you enter a formula that contains several operations—like adding, subtracting, and dividing—Excel 2003 knows to work these 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

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

Breakdown of a Complex Formula

Let's take a look at another example:

2*(6-4) =?

Is the answer 8 or 4? If you ignored the parentheses and calculated in the order in which the numbers appear, 2*6-4, you'd get the wrong answer, 8. You must follow the order of operations to get the correct answer.

To calculate the correct answer:

  • Calculate the operation in parenthesis (6-4), where the answer is 2.
  • Multiply the answer obtained in step 1, which is 2, to the numeric 2* that opened the equation. In other words, multiply 2*2.
  • The answer is 4.

When using formulas with cell references, the results change each time the numbers are edited.

Remember: In Excel, never do math "in your head" and type the answer in a cell where you would expect to have a formula calculate the answer.