MARISSA JONES ONLY!!!!

Insert a Function

Learning Outcomes

· Use the Insert Function button

· Select a range for use in a function

· Select a function from the AutoSum list arrow

Functions  are predefined worksheet formulas that enable you to perform complex calculations easily. You can use the Insert Function button on the formula bar to choose a function from a dialog box. You can quickly insert the SUM function using the AutoSum button on the Ribbon, or you can click the AutoSum list arrow to enter other frequently used functions, such as AVERAGE. You can also use the Quick Analysis tool to calculate commonly used functions. Functions are organized into categories, such as Financial, Date & Time, and Statistical, based on their purposes. You can insert a function on its own or as part of another formula. For example, you have used the SUM function on its own to add a range of cells. You could also use the SUM function within a formula that adds a range of cells and then multiplies the total by a decimal. If you use a function alone, it always begins with an equal sign ( = ) as the formula prefix.  You need to calculate the average expenses for the first quarter of the year and decide to use a function to do so.

Steps

1. 1

Click cell B15

This is the cell where you want to enter a calculation that averages expenses per country for the first quarter.

Quick Tip

When using the Insert Function button or the AutoSum list arrow, it is not necessary to type the equal sign (=); Excel adds it as necessary.

2. 2

Click the Insert Function button  on the formula bar

An equal sign ( = ) is inserted in the active cell and in the formula bar, and the Insert Function dialog box opens, as shown in  Figure 2-3 . In this dialog box, you specify the function you want to use by clicking it in the Select a function list. The Select a function list initially displays recently used functions. If you don’t see the function you want, you can click the Or select a category list arrow to choose the desired category. If you’re not sure which category to choose, you can type the function name or a description in the Search for a function field. The AVERAGE function is a statistical function, but you don’t need to open the Statistical category because this function already appears in the Most Recently Used category.

Figure 2-3Insert Function Dialog Box

Quick Tip

To learn about a function, click it in the Select a function list. The arguments and format required for the function appear below the list.

3. 3

Click AVERAGE in the Select a function list if necessary, read the information that appears under the list, then click OK

The Function Arguments dialog box opens, in which you define the range of cells you want to average.

Quick Tip

When selecting a range, remember to select all the cells between and including the two references in the range.

4. 4

Click the Collapse button  in the Number1 field of the Function Arguments dialog box, select the range B4:B11 in the worksheet, then click the Expand button  in the Function Arguments dialog box

Clicking the Collapse button minimizes the dialog box so that you can select cells in the worksheet. When you click the Expand button, the dialog box is restored, as shown in  Figure 2-4 . You can also begin dragging in the worksheet to automatically minimize the dialog box; after you select the desired range, the dialog box is restored.

Figure 2-4Expanded Function Arguments Dialog Box

5. 5

Click OK

The Function Arguments dialog box closes, and the calculated value is displayed in cell B15. The average expenses per country for Quarter 1 is 4453.0813.

6. 6

Click cell C15, click the AutoSum list arrow  in the Editing group on the Home tab, then click Average

A ScreenTip beneath cell C15 displays the arguments needed to complete the function. The text “number1” is in boldface, telling you that the next step is to supply the first cell in the group you want to average.

7. 7

Select the range C4:C11 in the worksheet, then click the Enter button  on the formula bar

The average expenses per country for the second quarter appear in cell C15.

8. 8

Drag the fill handle from cell C15 to cell E15

The formula in cell C15 is copied to the rest of the selected range, as shown in  Figure 2-5 .

Figure 2-5Average Functions Used in Worksheet

9. 9

Save your work