To accomplish more sophisticated numerical and text processing operations in your worksheets, Excel allows you to add built-in calculations called functions to your formulas. A function is a predefined equation that operates on one or more values and returns a single value. Excel includes a collection of over 200 functions in several useful categories, as shown in Table 20-2. For example, you can use the PMT (payment) function from the Financial category to calculate the periodic payment for a loan based on the interest rate charged, the number of payments desired, and the principal amount.
For more information on the PMT function, see "Using PMT to Determine Loan Payments"
Each function must be entered with a particular syntax, or structure, so that Excel can process the results correctly. For example, the PMT function has a function syntax that looks like this:
Table 20-2. Categories of Excel Functions
|Financial||Loan payments, appreciation, and depreciation|
|Date & Time||Calculations involving dates and times|
|Math & Trig||Mathematical and trigonometric calculations like those found on a scientific calculator|
|Statistical||Average, sum, variance, and standard-deviation calculations|
|Lookup & Reference||Calculations involving tables of data|
|Database||Working with lists and external databases|
|Text||Comparing, converting, and reformatting text in cells|
|Logical||Calculations that produce the result TRUE or FALSE|
|Information||Determining whether an error has occurred in a calculation|
The abbreviated words shown between the parentheses are called arguments. In this function, rate is the interest rate, nper is the number of payments you'll make, and pv is the principal amount. To use this function correctly, you must specify a value for each required argument, and you must separate each argument with a comma. The arguments shown in bold are required, and the others are optional. (In the online Help, the optional arguments are also in bold.) For example, to use the PMT function to calculate the loan payment on a $1,000 loan at 19 percent annual interest over 36 months, you could type the following formula:
When Excel evaluates this function, it places the answer ($36.66) in the cell containing the function. (The answer is negative, indicated by the parentheses, because it is money you must pay out.) Note that the first argument (the interest rate) was divided by 12 in this example to create a monthly rate for the formula. This demonstrates an important point— you can use other calculations, including other functions, as the arguments for a function. Although it takes a little time to master how these arguments are structured, you'll find that functions produce results that can otherwise take hours to calculate by hand.
Perhaps the most useful function in Excel's collection is SUM, which totals the range of cells you select. Because SUM is used so often, the AutoSum button appears on the Standard toolbar to make adding numbers faster. In the following example, we'll use the AutoSum button to sum the Subtotal column in the order-form worksheet.
To total a column of numbers using the SUM function, follow these steps:
Excel places the SUM function on the formula bar, and (if possible) automatically selects a range of neighboring cells as an argument for the function. If you selected a cell directly below a column of numbers, your screen will look similar to the one in Figure 20-2.
Figure 20-2. The AutoSum button inserts the SUM function and automatically suggests the cells to use for the argument.
Use SUM to Add Nonadjacent Ranges
You can use the SUM function to add multiple noncontiguous ranges by separating the cell ranges with commas. For example, =SUM(A3:A8,B3:B8) adds six cells in column A to six cells in column B and displays the total. You might find it easier to use the mouse and select cells by clicking each cell or range of cells while pressing the Ctrl key.
With so many functions to choose from, it might seem daunting to experiment with unfamiliar features on your own. Excel makes it easier by providing a special command named Function on the Insert menu, to help you learn about functions and enter them into formulas. The Paste Function dialog box, shown in Figure 20-3, lets you browse through the nine function categories and pick just the calculation you want.
You can also use Office Assistant to help you learn how each function works and what arguments it requires. (More than 200 functions are carefully documented in the online Help.) When you double-click a function in the Function Name list box, Excel displays a second dialog box prompting you for the required arguments. Give it a try now with a useful statistical function called AVERAGE.
Figure 20-3. The Paste Function dialog box lists function names by category.
To use AVERAGE to calculate the average of a list of numbers, follow these steps:
You can save time opening the Paste Function dialog box by clicking the Paste Function button on the Standard toolbar.
You can include one function as an argument in another function if the result is compatible. For example, the formula =SUM(5,SQRT(9)) adds together the number 5 and the square root of 9, and then displays the result (8).