Doing Higher Math with Functions

Functions are ready-made formulas you can use to perform a series of operations using two or more values or a range of values. For example, to determine the sum of a series of values in cells A5 to G5, you can enter the function =SUM(A5:G5) rather than =A5+B5+C5+D5+E5+F5+G5. Other functions can perform more complex operations, such as determining the monthly payment on a loan when you supply the values for the principal, interest rate, and number of payment periods.

Every function must have the following three elements:

• The equal sign (=) : Indicates that what follows is a formula or function and not a label, value, or date.

• The function name : Indicates the type of operation you want Excel to performfor example, SUM.

• The argument : Indicates the cell addresses of the values on which the function actsfor example, A3:F11. The argument is often a range of cells, but it can be much more complex.

A function can be part of another formula. For example, =SUM(A3:A9)+B43 uses the SUM function along with the addition operator to add the value in cell B43 to the total of the values in cells A3 to A9.

Using the AutoSum Tool

One of the tasks you perform most often is totaling a row or column of values. To simplify the process, Excel provides a tool devoted to determining totals: AutoSum .

To quickly determine the total of a row or column of values, first click an empty cell to the right of the row or just below the column of values. Then click the AutoSum button in the Standard toolbar, as shown in Figure 6.8. AutoSum assumes you want to add the values in the cells to the left of or above the currently selected cell, so it displays a marching ants box (called a marquee ) around those cells. If AutoSum selects an incorrect range of cells, you can edit the selection by dragging over the cells whose values you want to total. When the AutoSum formula is correct, press Enter or click another cell.

Figure 6.8. With the click of a button, AutoSum determines the total.

If your worksheet has two or more cells that contain subtotals, you also can use AutoSum to determine the grand total. Click the cell in which you want to insert the grand total, and then click the AutoSum button. Click the first subtotal , and then Ctrl+click any additional subtotals you want to include in the grand total. Press Enter .

Note that the AutoSum button doubles as a drop-down list. Click the arrow to the right of the button to access these additional functions:

• Average totals the values in the selected cells and divides by the number of values totaled to determine the average value.

• Count tallies the number of selected cells to determine the number of entries.

• Max compares all values in the selected cells and displays the highest value.

• Min compares all values in the selected cells and displays the lowest value.

• More Functions displays the Insert Function dialog box, which leads you through the process of selecting a function and constructing the required argument. See the following section, "Entering Functions with the Insert Function Dialog Box."

Entering Functions with the Insert Function Dialog Box

The SUM and AVERAGE functions are fairly easy to enter because their arguments consist merely of a string of numbers . Some of the other functions, howeversuch as the financial function that determines the payment on a loancontain several values and require you to enter those values in the proper syntax (order). To type the function, you must remember its name and know the required syntax, which can be quite difficult. The Insert Function dialog box can simplify the process greatly.

To use the Insert Function dialog box to paste a function into a cell, follow these steps:

1. Select the cell in which you want to insert the function.

2. Open the Insert menu and select Function , or click the arrow to the right of the AutoSum button and click More Functions , or click the fx button in the formula bar. The Insert Function dialog box appears, as shown in Figure 6.9, displaying a list of available functions.

   Figure 6.9. The Insert Function dialog box enables you to select the function rather than type it.

   

3. Perform one of the following steps:

   In the Search for a Function box, type a description of what you want the function to do, and then click the Go button or press Enter .

   Open the Or Select a Category list and select the type of function you want to insert. If you're not sure, select All to display the names of all the functions; they are listed alphabetically .

4. Select the function you want to insert from the Select a Function list, and click OK . The Function Arguments dialog box appears, as shown in Figure 6.10, prompting you to type the argument. You can type values or cell addresses in the various text boxes. Alternatively, you can click the button to the right of the text box and then click the cell that contains the specified value.

   Figure 6.10. Enter the values and cell references that make up the argument.

   

5. Enter the values or cell ranges for the argument. You can type a value or an argument, or click the cells that contain the required values. (Some arguments, such as those that start with "If," are optional. Excel must "decide" which action to perform based on entries in your worksheet.)

6. Click OK or press Enter . Excel inserts the function and argument in the selected cell and displays the result.

When you need to edit a function, select the cell that contains the function you want to edit. (Make sure you're not in Edit mode; that is, the insertion point should not appear in the cell. If the insertion point appears in the cell, click a different cell, and then click this cell again.) Open the Insert menu and select Function , or click the Insert Function button. This displays the Function Arguments dialog box, which helps you edit your argument.

Note

When you select a function in the Select a Function list, Excel displays a description of the function. Read this description to learn the purpose of the function.

Caution

If you click a button for one of the items in the Function Arguments dialog box, Excel tucks the dialog box out of the way, displaying the address of the currently selected cell and a button for bringing the dialog box back into view. After you select the desired cell, click the button to the right of the cell address to redisplay the dialog box.

Seeing a Loan Function in Action

To get some hands-on experience with functions, let's take a look at the PMT ( payment ) function. This function determines the monthly payment on a loan based on the loan amount, rate, and payment periods. Here's how the function and its arguments appear:

=PMT(rate,nper,pv,fv,type)

Let's break this down:

= is an essential element in the function statement, as explained earlier.

PMT is the function name, which stands for "payment."

rate is the percentage rate of the loan per period. In other words, if you take out a 30-year mortgage at 6.5% and plan to make payments every month, the rate is 6.5%/12 or .541666%.

nper is the number of payment periods. For example, on a 30-year mortgage, 12 payments are due per year, so the total number of payment periods would be 360.

pv , which stands for "present value," is the total amount you plan to borrow . This amount must be entered as a negative value. For example, if you're taking out a $120,000 loan, you would enter -120000 .

fv , which stands for "future value," is zerothat is, the amount the loan will be worth after you pay the last payment.

type specifies the day in the payment period on which you are required to submit the payment0 (or omit the type entry) for the last day of the payment period or 1 for the first day.

You can type a function using values in place of the codes. For example, to determine the payment on a $120,000, 30-year loan, at 6.5%, you could type the following:

=PMT(.065/12,360,-120000,0,0)

Or, you could use cell references in place of the values. Figure 6.11 shows a sample worksheet used to determine the monthly mortgage payment on a personal loan. Note that each value in the typed example is in a separate cell, and that the function uses cell references in place of actual values.

Figure 6.11. Use cell references instead of values.