Creating Complex Formulas

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

Complex formulas

Video: Creating Complex Formulas in Excel 2010

Launch "Creating Complex Formulas with Excel 2010" video!Watch the video (4:56).

Simple formulas have one mathematical operation, such as 5+5. Complex formulas have more than one mathematical operation, such as 5+5-2. When there is more than one operation in a formula, the order of operations tells us which operation to calculate first. To use Excel to calculate complex formulas, you'll need to understand the order of operations.

Optional: You can download this example for extra practice.

The order of operations

Excel calculates formulas based on the following order of operations:

  1. Operations enclosed in parentheses
  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 the order is Please Excuse My Dear Aunt Sally.

Example 1

The following example demonstrates how to use the order of operations to calculate a formula:

Order of Operations example Order of Operations example

Example 2

In this example, we'll review how Excel will calculate a complex formula using the order of operations. The selected cell will display the percent of total Pete Lily seeds sold that were white.

Order of Operations Excel example Order of Operations Excel example
  1. First, Excel will calculate the amount sold in parentheses: (19*1.99)=37.81 White Pete Lily seeds and (33*1.99)=65.67 Total Pete Lily seeds.
  2. Second, it will divide the White Pete Lily seeds amount by the Total Pete Lily seeds amount: 37.81/65.67=.5758.
  3. Last, it will multiply the result by 100 to obtain the value as a percent: .5758*100=57.58.

Based on this complex formula, the result will show that 57.58% of the total Pete Lily seeds sold were white. You can see from this example that it is important to enter complex formulas with the correct order of operations. Otherwise, Excel will not calculate the results accurately.