Excel 2000

Complex Formulas

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

### Introduction

#### By the end of this lesson, you should be able to:

### Complex formulas

### Creating complex formulas

### Filling formulas

#### To use the fill handle:

### Copying and pasting formulas

While you can use both the fill method and the copy and paste method to copy a formula to surrounding cells, if you want to copy a formula to cells **beyond the cells** adjacent to the formula use **copy and paste**.#### To use copy and paste:

### Revising formulas

#### To use the range finder:

#### To revise a formula using the range finder:

#### To revise using the keyboard:

### Absolute references

#### To delete a formula:

#### To create an absolute reference:

### Did you know?

/en/excel2000/creating-formulas/content/

- Create complex formulas
- Fill a formula to another cell
- Copy and paste a formula to another cell
- Revise a formula
- Create an absolute reference

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 knows to work those operations in a specific order. The order of operations is:

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

Relative to the order, you will also calculate from left to right. Let's look at an example: **2*(6-4) =?**

Is the answer 8 or 4? If you calculated in the order in which the numbers appear, ignoring the parentheses, 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:**

1. Calculate the operation in parentheses: 6-4=2.

2. Multiply 2*2=4.

3. The answer is 4.

When using **formulas**, the results change each time the numbers are **edited**.

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.

When you create a complex formula, Excel automatically follows the order of operations. If you want a certain portion of the formula to be calculated first, put it in parentheses—for example, **=(B8+B9)*A10**.

- Enter the numbers you want to calculate.
- Click the cell where you want the formula
**result**to appear. - Type
**=**. - Click the
**first cell**to be included in the formula. The reference displays where you want your result. - Type a
**mathematical operator**(such as the addition symbol +). The operator displays in the cell and in the Formula bar. - Click the
**second cell**in the formula. The reference displays where you want your result. - Type the next
**operator**in the formula. The operator displays in the cell and un the Formula bar. - Click the
**third cell**to be included in the formula. The reference displays where you want your result. - Repeat steps 7 and 8 until the formula is complete, adding parentheses where necessary.
- Press
**Enter**or click the**Enter button**on the Formula bar to end the formula.

Try changing one of the values in the formula and watch the answer to the formula change.

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, read the Double-Check Your Formulas lesson from our Excel Formulas tutorial.

Once you've created a working formula, don't waste time typing and retyping it. Simply **fill** it to the other cells. To do this, you will need to recognize the mouse pointer's various shapes. When you move the mouse pointer around the Excel window, it changes shape and function.

If you'll be copying a formula **to a surrounding cell**, you can use the **Fill method**. The square box in the lower-right corner of the **cell pointer** is the **fill handle**.

- Click the cell that contains the formula.
- Position the mouse pointer on the lower-right corner of the beginning of the cell pointer (fill handle). The mouse pointer becomes a black crosshair.
- Click and hold the left mouse button and drag to select the next cells to be filled in. The cells are now selected.
- Release the mouse button. The formula is copied.

In Excel, the references change as the formula is copied. Remember, you use references to identify cells containing the numbers you want to calculate in a formula.

You can also use **copy and paste** to copy a formula to other cells. This will be discussed on the next page.

- Select the cell with the formula you want to copy.
- Click the
**Copy**button. Marching ants appear around the copied cells. - Click the area where you want to place the duplicated formula.
- Press
**Enter**. The formula is copied to the new location.

Cut, copy, and paste are discussed in detail in our Office 2000 tutorial.

If you entered an incorrect formula or added new data and need to change the formula to reflect the new entries, you can **revise** your formula using the range finder or your keyboard.

- Double-click the cell that contains the formula you want to edit.
- Excel colors each cell address or range address in the formula a different color (see B4 below).
- Corresponding colored borders appear around the cell or range. Each colored border is called a
**range finder**(in B2 and B3 below). This makes it easy to see whether a formula refers to the correct cells.

- Select the cell or range you want to revise, and drag its
**range finder**(the mouse pointer should be an**arrow**) to the cell or range with which you want to replace it (see Figure 1 below). - Press
**Enter**or click the Enter button to complete the formula. - The new result now displays in the cell that contains the formula (see Figure 2 below).

- Double-click the cell that contains the formula you want to revise. The range finder displays.
- Select the cell or range you want to revise.
- Select another cell or range you want to replace it with.
- Press
**Enter**or click the Enter button. The result now displays in the cell that contains the formula.

You can also select the cell that contains the formula, select the part of the formula you want to revise, and type a new cell address. This is a somewhat inaccurate method, though, because it's easy to mistype.

Typically, when you copy a formula that uses references, Excel automatically adjusts the reference in the pasted formula to refer to different cells relative to the position of the formula.

Sometimes when you copy a formula, you don't want one of the cell references to change. Creating an **absolute reference**—a reference to a cell that does not change when copying a formula—solves this problem. An absolute reference always refers to the same cell or range. It is designated in the formula by the dollar sign (**$**).

For this exercise, delete the original formulas and start again.

- Select the cells in which the original formulas exist. The cells are now selected.
- Press
**Delete**. The information in these cells is deleted.

- Enter the numbers you want to calculate. Then, create a simple
**formula**(=A1+A2). - Click the cell in which you want to place the
**formula answer**. - To create an absolute reference, add a dollar sign (
**$**) in front of the**column reference**and the**row reference**(=$A$1+A2). - Press
**Enter**or click the Enter button. The answer displays in the cell. **Copy and paste**the formula to another adjacent cell. The formula now includes an absolute reference (=$A$1+C2).

Knowing the **order of operations** is essential to getting the correct answer. Spend some time memorizing it:

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

/en/excel2000/formatting-numbers/content/