MARISSA JONES ONLY!!!!

Create a Complex Formula

Learning Outcomes:

· Create a complex formula by pointing

· Use the fill handle and Auto Fill

A  complex formula  is one that uses more than one arithmetic operator. You might, for example, need to create a formula that uses addition and multiplication. In formulas containing more than one arithmetic operator, Excel uses the standard  order of precedence  rules to determine which operation to perform first. You can change the order of precedence in a formula by using parentheses around the part you want to calculate first. For example, the formula =4+2*5 equals 14, because the order of precedence dictates that multiplication is performed before addition. However, the formula =(4+2)*5 equals 30, because the parentheses cause 4+2 to be calculated first.  You want to create a formula that calculates a 20% increase in tour expenses.

Steps

1. 1

Start Excel, open the file EX 2-1.xlsx from the location where you store your Data Files, then save it as EX 2-R2G Tour Expense Analysis

2. 2

Select the range B4:B11, click the Quick Analysis tool  that appears below the selection, then click the Totals tab

The Totals tab in the Quick Analysis tool displays commonly used functions, as seen in  Figure 2-1 .

Figure 2-1Totals Tab in the Quick Analysis Tool

3. 3

Click the AutoSum button  in the Quick Analysis tool

The newly calculated value displays in cell B12 and has bold formatting automatically applied, helping to set it off as a sum. This shading is temporary, and will not appear after you click a cell.

4. 4

Click cell B12, then drag the fill handle to cell E12

The formula in cell B12, as well as the bold formatting, is copied to cells C12:E12.

Quick Tip

When the mode indicator on the status bar says “Point,” cells you click are added to the formula.

5. 5

Click cell B14, type =, click cell B12, then type +

In this first part of the formula, you are inserting a reference to the cell that contains total expenses for Quarter 1.

6. 6

Click cell B12, then type *.2

The second part of this formula adds a 20% increase (B12*.2) to the original value of the cell (the total expenses for Quarter 1).

7. 7

Click the Enter button  on the formula bar

The result, 42749.58, appears in cell B14.

8. 8

Press [Tab], type =, click cell C12, type +, click cell C12, type *.2, then click 

The result, 42323.712, appears in cell C14.

Quick Tip

You can also copy the formulas by selecting the range C14:E14, clicking the Fill button  in the Editing group on the Home tab, then clicking Right.

9. 9

Drag the fill handle from cell C14 to cell E14, then save your work

The calculated values appear in the selected range, as shown in  Figure 2-2 . Dragging the fill handle on a cell copies the cell’s contents or continues a series of data (such as Quarter 1, Quarter 2, etc.) into adjacent cells. This option is called  Auto Fill .

Figure 2-2Results of Copied Formulas

Using Add-ins to Improve Worksheet Functionality

Excel has more functionality than simple and complex math computations. Using the My Add-ins feature (found in the Add-ins group in the Insert tab), you can insert an add-in into your worksheet that accesses the web and adds functionality. Many of the add-ins are free or available for a small fee and can be used to create an email, appointment, meeting, contact, or task, or be a reference source, such as the Mini Calendar or Date Picker. When you click the My Add-ins button list arrow, you’ll see any Recently Used Add-ins. Click See All to display the featured Add-ins for Office and to go to the Store to view available add-ins. When you find one you want, make sure you’re logged in to Office.com, click the add-in, click Trust It, and the add-in will be installed. Click the My Add-ins button and your add-in should display under Recently Used Add-ins. Click it, then click Insert. The add-in will display in the Recently Used Add-ins pane when you click the My Add-ins button.

Reviewing the Order Of Precedence

When you work with formulas that contain more than one operator, the order of precedence is very important because it affects the final value. If a formula contains two or more operators, such as 4+.55/4000*25, Excel performs the calculations in a particular sequence based on the following rules: Operations inside parentheses are calculated before any other operations. Reference operators (such as ranges) are calculated first. Exponents are calculated next, then any multiplication and division—progressing from left to right. Finally, addition and subtraction are calculated from left to right. In the example 4+.55/4000*25, Excel performs the arithmetic operations by first dividing .55 by 4000, then multiplying the result by 25, then adding 4. You can change the order of calculations by using parentheses. For example, in the formula (4+.55)/4000*25, Excel would first add 4 and .55, then divide that amount by 4000, then finally multiply by 25.