Complex Formulas

Complex Formulas

Back to Tutorial
#### Lesson 14: Complex Formulas

### Introduction

#### The order of operations

#### Creating complex formulas

#### To create a complex formula using the order of operations:

### Challenge!

/en/excel2013/simple-formulas/content/

A simple formula is a mathematical expression with one operator, such as **7+9**. A **complex formula** has more than one mathematical operator, such as** 5+2*8**. When there is more than one operation in a formula, the **order of operations** tells Excel which operation to calculate first. In order to use Excel to calculate complex formulas, you will need to understand the order of operations

Optional: Download our practice workbook.

Excel calculates formulas based on the following **order of operations**:

- Operations enclosed in
**parentheses** **E****xponential**calculations (3^2, for example)**M****ultiplication**and**division**, whichever comes first**A****ddition**and**subtraction**, whichever comes first

A mnemonic that can help you remember the order is **PEMDAS**, or **P**lease **E**xcuse **M**y **D**ear **A**unt **S**ally.

Click the arrows in the slideshow below to learn more about how the order of operations is used to calculate formulas in Excel.

In the example below, we will demonstrate how Excel solves a complex formula using the order of operations. Here, we want to calculate the cost of **sales tax** for a catering invoice. To do this, we'll write our formula as **=(D2+D3)*0.075** in cell** D4**. This formula will add the prices of our items together and then multiply that value by the 7.5% tax rate (which is written as 0.075) to calculate the cost of sales tax.

Creating a complex formula

Excel follows the order of operations and first adds the values inside the parentheses: **(44.85+39.90) = $84.75**. It then multiplies that value by the tax rate: **$84.75*0.075**. The result will show that the sales tax is **$6.36**.

The completed formula and calculated value

It is especially important to enter complex formulas with the correct order of operations. Otherwise, Excel will not calculate the results accurately. In our example, if the **parentheses** are not included, the multiplication is calculated first and the result is incorrect. Parentheses are the best way to define which calculations will be performed first in Excel.

Result of an incorrect formula

In our example below, we will use **cell references **along with **numerical values** to create a complex formula that will calculate the **total cost** for a catering invoice. The formula will calculate the cost for each menu item and then add those values together.

- Select the
**cell**that will contain the formula. In our example, we'll select cell**C4**.Selecting cell C4 - Enter your
**formula**. In our example, we'll type**=B2*C2+B3*C3**. This formula will follow the order of operations, first performing the multiplication:**2.29*20****= 45.80**and**3.49*35 = 122.15**. It then will add those values together to calculate the total:**45.80+122.15**.Creating a complex formula - Double-check your formula for accuracy, then press
**Enter**on your keyboard. The formula will**calculate**and display the**result**. In our example, the result shows that the total cost for the order is**$167.95**.The completed formula and calculated value

You can add **parentheses** to any equation to make it easier to read. While it won't change the result of the formula in this example, we could enclose the multiplication operations within parentheses to clarify that they will be calculated before the addition.

Including parentheses in a formula for clarity

Excel **will not always tell you** if your formula contains an error, so it's up to you to check all of your formulas. To learn how to do this, you can read the Double-Check Your Formulas lesson from our Excel Formulas tutorial.

- Open an existing Excel workbook. If you want, you can use our practice workbook.
- Create a complex formula that will perform addition before multiplication. If you are using the example, create a formula in cell
**D6**that first**adds**the values of cells**D3**,**D4**, and**D5**and then**multiplies**their total by**0.075**. Hint: You'll need to think about the order of operations for this to work correctly.

/en/excel2013/relative-and-absolute-cell-references/content/