Excel 2007

Creating Complex Formulas

Back to Tutorial
#### Lesson 8: Creating Complex Formulas

### Introduction

### Complex formulas

#### Complex formulas defined

#### The order of operations

#### To calculate the correct answer:

### Complex formulas

### Creating complex formulas

#### Example of how to write a complex formula:

### What is an absolute reference?

#### To create an absolute reference:

### Challenge!

/en/excel2007/printing-workbooks/content/

Excel is a spreadsheet application and is intended to be used to **calculate** and **analyze numerical information** such as household budgets, company finances, and inventory. To do this, you need to understand **formulas**.

In this lesson, we’ll discuss **complex formulas** that use multiple mathematical operators, as well as those that use **absolute** and **relative references**.

Download the example to work along with the video.

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 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:

- Operations enclosed in parenthesis
- Exponential calculations (to the power of)
- Multiplication and division, whichever comes first
- Addition and subtraction, whichever comes first

A mnemonic that can help you remember this is **P**lease **E**xcuse **M**y **D**ear **A**unt **S**ally (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.

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

Before moving on, let's explore some more formulas to make sure you understand the order of operations by which Excel calculates the answer.

4*2/4 | Multiply 4*2 before performing the division operation because the multiplication sign comes before the division sign. The answer is 2. |

4/2*4 | Divide 4 by 2 before performing the multiplication operation because the division sign comes before the multiplication sign. The answer is 8. |

4/(2*4) | Perform the operation in parentheses (2*4) first, and divide 4 by this result. The answer is 0.5. |

4-2*4 | Multiply 2*4 before performing the subtraction operation because the multiplication sign is of a higher order than the subtraction sign. The answer is -4. |

Excel **automatically** follows a **standard order of operations** in a complex formula. If you want a certain portion of the formula to be calculated first, put it in parentheses.

- Click the cell where you want the formula
**result**to appear. In this example, H6. - Type the equals sign (=) to let Excel know a formula is being defined.
- Type an open parenthesis, or (.
- Click on the
**first cell**to be included in the formula (G6, for example). - Type the
**addition sign (+)**to let Excel know that an add operation is to be performed. - Click on the
**second cell**in the formula (G7, for example). - Type a closed parentheses ).

- Type the next mathematical operator, or the
**division symbol (/)**, to let Excel know a division operation is to be performed. - Type an open parenthesis, or (.
- Click on the
**third cell**to be included in the formula (D6, for example). - Type the
**addition sign (+)**to let Excel know that an add operation is to be performed. - Click on the
**fourth cell**to be included in formula (D7, for example). - Type a closed parentheses ).

**Important:**Press**Enter**, or click the**Enter button**on the Formula bar. This step ends the formula.

To show fewer decimal places, you can click the **Decrease Decimal** place command on the Home tab.

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.

In earlier lessons, we saw how **cell references** in formulas **automatically adjust** to new locations when the formula is pasted into different cells. This is called a **relative reference**.

Sometimes when you copy and paste a formula, you don't want one or more cell references to change. An** absolute reference** solves this problem. **Absolute cell references** in a formula **always** refer to the **same cell** or cell range in a formula. If a formula is copied to a different location, the absolute reference remains the same.

An absolute reference is designated in the formula by the addition of a **dollar sign ($)**. It can precede the column reference or the row reference, or both. Examples of absolute referencing include:

- Select the cell where you wish to write the formula (in this example, H2).
- Type the equals sign (=) to let Excel know a formula is being defined.
- Click on the
**first cell**to be included in the formula (F2, for example). - Enter a mathematical operator (use the multiplication symbol for this example).
- Click on the
**second cell**in the formula (C2, for example). - Add a $ sign before the C and a $ sign before the 2 to create an absolute reference.

- Copy the formula into H3. The new formula should read =F3*$C$2. The F2 reference changed to F3 because it is a relative reference, but C2 remained constant because you created an absolute reference by inserting the dollar signs.

When writing a formula, you can press the **F4** key on your keyboard to switch between relative and absolute cell references. This is an easy way to quickly insert an absolute reference.

Use the Inventory or any Excel workbook you choose to complete this challenge.

- Create
**at least one**complex formula that uses the**addition**and**division**operations. - Create
**at least****one**complex formula that uses**parentheses**and a**multiplication**operation. - Create a formula that uses an
**absolute**reference.

/en/excel2007/working-with-basic-functions/content/