Functions can be entered manually, but to do this you need to know the name of the function and the syntax for the arguments.
In Excel 2002, Excel 2003, Excel 2007, and Excel 2010, if you enter the name of the function, syntax-specific help is displayed to help you complete the parameters. Programmers who work with Microsoft Visual Basic for Applications are already familiar with this type of help, which displays a ScreenTip as soon as you enter the function name and the opening parenthesis (see Figure 4-6). Each argument in turn is shown in bold as you enter the individual parameters.
If you are familiar with a function, you can probably enter the function faster without assistance. The section titled Viewing Arguments, later in this chapter, explains how the syntax information is displayed in the versions of Excel prior to Excel 2002.
In Excel 2007 and Excel 2010 the support for entering formulas was further improved. If you start typing a formula, a list with suggestions appears (see Figure 4-7).
Figure 4-7. Excel 2007 and Excel 2010 suggest functions starting with “I” and display a description.
The Function Wizard helps you select a function and enter the appropriate parameters. The Function Wizard button disappeared from the standard toolbar in Excel 2002 and was replaced by more options in the AutoSum menu, but the functionality is still available in Excel 2003, Excel 2007, and Excel 2010.
To start the Function Wizard, use one of the following methods:
On the Insert menu, select Function (Excel 2003) or click the Insert Function button on the Formulas tab (Excel 2007 and Excel 2010).
Click the Insert Function button in the edit box (see Figure 4-8).
Select More Functions in the AutoSum list on the standard toolbar (Excel 2003) or on the Home tab (Excel 2007 and Excel 2010).
The Function Wizard helps you enter functions and offers explanations of the arguments. During the entry process, you can terminate the creation of a function at any time by clicking the Cancel button in the Function Wizard or by pressing the Esc key.
Assume that you want to calculate the monthly repayments for a loan of $100,000 over a period of ten years and at an interest rate of 7 percent. Of course you can use a function for this calculation (see Figure 4-9).
Select cell C6 to insert the function.
Click the Insert Function button in the edit box or select the Insert/Function menu command. The Function Wizard’s Insert Function dialog box opens.
Click the function type you want in the Or Select A Category box. In this instance, click Financial (see Figure 4-10). When you select the category, the large number of available functions is filtered to the appropriate subset of options.
Select the PMT function in the Select A Function box. The general syntax for the function and a short description are displayed at the bottom of the dialog box. Click OK.
The next dialog box provides a text box for each function argument. The required arguments appear in bold, and the optional arguments appear in normal type (see Figure 4-11).
Complete the text boxes according to Figure 4-11.
To add the function to the table, click OK. The formula is
=PMT(C3/12,C4*12,C2). The result is a monthly installment of $1,161.08 displayed as a negative number.To display the result as a positive number, set the present value, Pv, as –C2.
The interest is the annual percentage rate and has been divided by 12 to provide the monthly interest rate. In the Nper argument, you have to multiply the duration of 10 years by 12 to obtain the total number of monthly installments (120).
A question frequently asked by Excel users is, “How can I find out if Excel provides a function for my problem?” This book was written to address this problem, but the Function Wizard can also offer some help.
Open the Function Wizard and enter your question in the Search For A Function box (see Figure 4-12).
A selection of suggested functions will be offered. Some of the suggestions will not be useful, but this facility provides a good starting point and searches for functions across all categories (see Figure 4-13).
When you are entering function parameters in the Function Arguments dialog box, to the right of each text box is a small button with a red arrow (see Figure 4-14).
If you click this button, the rest of the dialog box is hidden, leaving just the text box and allowing more of the underlying spreadsheet to be viewed and selected. This feature allows you to complete cell references simply by selecting cells on the sheet.
All general selection rules apply: If you select a range, the range operator (:) is inserted, and for single cells (use the Ctrl key and click to select two or more single cells), the combination operator (,) is used. This method is not only easier than entering information manually but also helps to avoid errors.
To return to the full Function Wizard view, click the button at the right end of the text box again.
The Function Wizard also allows quick access to the Excel Help facility. Excel Help explains each function with all arguments and provides detailed examples. The Help facility has improved considerably since Excel 2002, and although this section focuses on the Excel 2007 Help facility, help for functions in older versions of Excel can easily be accessed.
The Function Wizard displays tips for the selected function when you click the Help On This Function link in the lower-left corner of the dialog box, which opens the corresponding help topic (see Figure 4-15).
When defining a function, you often need to specify a function as one of the arguments of the function. This is known as nesting functions.
For example, assume that you want to calculate the sine of an angle. The angle is specified in degrees (see Figure 4-16). Because the sine function in Excel expects a radian measure, you have to use the RADIANS function. This is a nested function because you use both functions.
To enter a nested function, perform the following steps:
Select the cell in which you want to enter the formula (in this example, C4). Select the Insert Function command.
In the Or Select A Category list, click Math & Trig.
In the Select A Function list, click SIN, and then click OK.
To enter a function instead of an argument into a text box, click the arrow in the Name Box at the left end of the formula bar. For this example, select the RADIANS function (see Figure 4-17) to insert it into the SIN function argument text box.
The Function Wizard now shows the RADIANS function. You can view the status of the formula in the edit box (see Figure 4-18).
Enter the cell reference C2 as the argument for the RADIANS function. Click OK to exit.
The finished formula is
=SIN(RADIANS(C2))
For a 150° angle, the formula calculates the sine as 0.5.
Important
Click OK in the second nested function to complete the formula entry. If you use nested functions and need to return to either function, click the name of the function in the formula bar, and then click the Insert Function button. The dialog box displays the arguments of the selected function for editing.
You can manually edit a function in the formula bar. After you make a change, press the Enter key. To directly edit the function in the cell, double-click in the cell or press the F2 key.
You can also use the Function Wizard to edit formulas with functions. To do this, click in the cell containing the function and then click the Insert Function button in the formula bar or select the Insert/Function menu option. Change the arguments as required. Click OK to close the dialog box.
See Also
Read the tips in Chapter 3, for information about entering, testing, and modifying formulas.