Using Functions Using Excel�fs Built-In Function Reference . . . . . . . . . . . . . . . . . . . . . . . 401 Installing the Analysis ToolPak . . . . . . . 403 Exploring the Syntax of Functions . . . . . 404 Inserting Functions . . . . . . . . . . . . . . . . 406 Worksheet functions are special tools that perform complex calculations quickly and easily. They work like the special keys on sophisticated calculators that compute square roots, logarithms, and statistical evaluations.except that Microsoft Excel has hundreds of these special functions. Some functions, such as SIN and FACT, are the equivalent of lengthy mathematical formulas that you would otherwise have to create by hand. Other functions, such as IF and VLOOKUP, can�ft be otherwise duplicated by formulas. When none of the built-in functions is quite what you need, you can create custom functions, as explained in Chapter 32, �gCreating Custom Functions.�h Using Excel�fs Built-In Function Reference While preparing this book, we had to make some tough choices. Fully describing each of the hundreds of worksheet functions would fill an entire book.or two, perhaps. To provide the greatest benefit, we had to decide which functions to focus on and which to mention only briefly. Admittedly, we tend to devote more ink to financial, information, and lookup functions than we do to engineering or trigonometric functions. We think this makes sense for the majority of our readers. If you need more information about functions that we do not cover in great detail, Excel offers several built-in resources: �� The online Help system. The online Help system includes a detailed description of each worksheet function. Just press F1 to display the Microsoft Excel Help task pane, and then type a function name into the Search box to find all the relevant Help topics. You can also click the Table of Contents link in the task pane, click on Working With Data, and then select the Function Reference, where the functions are grouped into functional categories to help you find the one you need, as shown in Figure 13-1. �� The Insert Function dialog box. This dialog box, shown in Figure 13-5 on page 406, can be used to search through the entire list of functions if you�fre not sure which function you need. To display the Insert Function dialog box, click the Insert Function button in the formula bar. �� The Function Arguments dialog box. This dialog box, shown in Figure 13-2, provides details about the function, and the required arguments appear in the middle of the dialog box. Notice that there is also a link to the relevant Help topic at the bottom of the dialog box. To display the Function Arguments dialog box, first select a cell containing a function and then click the Insert Function button on the formula bar. You can also click the Insert Function button while you are in the process of entering a formula after you type in a valid function name and an open parenthesis. f13ie02 Figure 13-2. Click the Insert Function button for instant assistance with an existing function or to find a function that fills your need. Insert Function button �� Function ScreenTips. These (yellow tags that appear below formulas) are useful if you are unsure about the syntax of a function as you enter a formula, and you can get help without even leaving the cell. After you type the required open parenthesis following any valid function name, the appropriate ScreenTip appears, as shown in Figure 13-3. The ScreenTip shows you the correct function syntax and also includes a link to the Help topic on the selected function. Just click the function name in the ScreenTip to display the relevant topic from the online Help system. If you click an argument name in the ScreenTip, the corresponding section of the formula is highlighted for you, making it easy to identify each argument, as shown in Figure 13-3. (To turn off this feature, choose Tools, Options, click the General tab, and under Settings, clear the Function ScreenTips check box.) Installing the Analysis ToolPak When you install Excel, the most commonly used functions are installed as well. However, there are additional functions available in the Analysis ToolPak, a set of add-in tools and functions designed for data analysis. This add-in includes a number of excellent worksheet functions that become available through the Insert Function dialog box, and some sophisticated, macro-based, statistical analysis tools. To see if you have the Analysis ToolPak installed, go to the Tools menu. If the Data Analysis command is there (you might have to double-click the menu name to expand it to its full size), then you�re good to go. The Data Analysis dialog box is shown on the right in Figure 13-4. If the Data Analysis command isn�t on your Tools menu, choose Tools, Add-Ins. If Analysis ToolPak is on the list of available add-ins, select it, and then click OK to install them. If the Analysis ToolPak isn�t in the list of available add-ins, you�ll need to run Setup to install it. For more information on the Analysis ToolPak, see Chapter 17, �Functions for Analyzing Statistics.� For more about Setup, see Appendix A, �Installing Microsoft Excel.� Exploring the Syntax of Functions Worksheet functions have two parts: the name of the function and the argument(s) that follow. Function names�such as SUM and AVERAGE�describe the operation that the function performs. Arguments specify the values or cells to be used by the function. For example, the function ROUND has the following syntax: =ROUND(number, num_digits) as in the formula =ROUND(M30,2). The M30 is a cell reference entered as the number argument� the value to be rounded. The 2 is the num_digits argument. The result of this function is a number (whatever M30 happens to be) rounded to two decimal places. Parentheses surround function arguments. The opening parenthesis must appear immediately after the name of the function. If you add a space or some other character between the name and the opening parenthesis, the error value #NAME? appears in the cell. Note A few functions, such as PI, TRUE, and NOW have no arguments. (These functions are usually nested in other formulas.) Even though they have no arguments, they must be followed by an empty set of parentheses, as in =NOW( ). When you use more than one argument in a function, you separate the arguments with commas. For example, the formula =PRODUCT(C1,C2,C5) tells Excel to multiply the numbers in cells C1, C2, and C5. Some functions, like PRODUCT and SUM, take an unspecified number of arguments. You can use as many as 30 arguments in a function, as long as the total length of the formula does not exceed 1,024 characters. However, you can use a single argument, or a range that refers to any number of cells in your worksheet, as a formula. For example, the function =SUM(A1:A5,C2:C10,D3:D17) has only three arguments but actually totals the values in 29 cells. (The first argument, A1:A5, refers to the range of five cells from A1 through A5, and so on.) The referenced cells can, in turn, also contain formulas that refer to more cells or ranges. Expressions as Arguments You can use combinations of functions to create an expression that Excel evaluates to a single value and then interprets as an argument. For example, in the formula =SUM(SIN(A1*PI( )),2*COS(A2*PI( ))) the comma separates two complex expressions that are evaluated and used as the arguments of the SUM function. Types of Arguments In the examples presented so far, all the arguments have been cell or range references. You can also use numbers, text, logical values, range names, arrays, and error values as arguments. Numeric Values The arguments to a function can be numeric. For example, the SUM function in the formula =SUM(327,209,176) adds the numbers 327, 209, and 176. Usually, however, you enter the numbers you want to use in cells of a worksheet and then use references to those cells as arguments to your functions. Text Values You can also use text as an argument to a function. For example, in the formula =TEXT(NOW( ),"mmm d, yyyy") in the second argument to the TEXT function, mmm d, yyyy, is a text argument specifically recognized by Excel. It specifies a pattern for converting the serial date value returned by NOW into a text string. Text arguments can be text strings enclosed in quotation marks or references to cells that contain text. For more on text functions, see �Understanding Text Functions� on page 416. Logical Values The arguments to a few functions specify only that an option is either set or not set; you can use the logical values TRUE to set an option and FALSE to specify that the option isn�t set. A logical expression returns the value TRUE or FALSE to the worksheet or the formula containing the expression. For example, the first argument of the IF function in the formula =IF(A1=TRUE,"Future ", "Past ")&"History" is a logical expression that uses the value in cell A1. If the value in A1 is TRUE, the expression A1=TRUE evaluates to TRUE, the IF function returns Future, and the formula returns the text Future History to the worksheet. For more on logical functions, see �Understanding Logical Functions� on page 421. Named References You can use a range name as an argument to a function. For example, if you use the Insert, Name, Define command to assign the name QtrlyIncome to the range C3:C6, you can use the formula =SUM(QtrlyIncome) to total the numbers in cells C3, C4, C5, and C6. For more on names, see �Naming Cells and Cell Ranges� on page 368. Arrays You can use an array as an argument in a function. Some functions, such as TREND and TRANSPOSE, require array arguments; other functions don�t require array arguments but do accept them. Arrays can be composed of numbers, text, or logical values. For more on arrays, see �Using Arrays� on page 385. Mixed Argument Types You can mix argument types within a function. For example, the formula =AVERAGE(Group1,A3,5*3) uses a range name (Group1), a cell reference (A3), and a numeric expression (5*3) to arrive at a single value. All three are acceptable. Inserting Functions The easiest way to locate and insert built-in functions is by clicking the Insert Function button. When you do, the dialog box shown in Figure 13-5 appears. f13ie05 Figure 13-5. Find a function using the Insert Function dialog box. Click the Insert Function button to access any of Excel�s built-in functions If you�re not sure what function you need, enter a description of what you are trying to do into the Search box. For example, if you enter �I want to know how many cells contain values� and then press Go, the Insert Function dialog box returns a list of recommended functions, similar to the list shown in Figure 13-6. As it turns out, the first function in the list of suggestions fills the bill. If you don�t find the function you�re looking for, try rewording your query. f13ie06 Figure 13-6. Ask a question in the Search box, and Excel suggests some possible functions you can try. You can also select a function category from the drop-down list to display all the applicable functions available. Function categories include Financial, Date & Time, Lookup & Reference, Text, and more. The Recommended category keeps track of any functions returned as a result of using the Search box. When you select a function, the syntax and a brief description appear at the bottom of the dialog box. You can obtain help on a function selected in the Select A Function list by clicking the Help On This Function link at the bottom of the dialog box. When you select a function and click OK, Excel enters an equal sign to start a formula in the active cell, inserts the function name and a set of parentheses, and displays the Function Arguments dialog box, shown in Figure 13-7. f13ie07 Figure 13-7. The selected function appears in the Function Arguments dialog box, where you can find more information about the function and each selected argument. Tip Shrink the Function Arguments Drag the Function Arguments dialog box around on the screen if you need to see the cells under it. For maximum viewing, shrink the dialog box by clicking one of the collapse buttons at the right side of the argument boxes. The Function Arguments dialog box contains one box for each argument of the selected function. If the function accepts a variable number of arguments (such as SUM), the dialog box grows as you enter additional arguments. A description of the argument box currently containing the insertion point appears near the bottom of the dialog box. To the right of each argument box, a display area shows the current value of the argument. This display is very handy when you are using references or defined names, as the value of each argument is calculated for you. The current value of the function (Formula Result) appears at the bottom of the dialog box. Some functions, such as INDEX, have more than one form. When you select a function from the Insert Function dialog box that has more than one form, Excel presents the Select Arguments dialog box, shown in Figure 13-8, in which you select the form you want to use. f13ie08 Figure 13-8. If a function has more than one form, the Select Arguments dialog box appears. Tip Get quick assistance with arguments Type an equal sign followed by the function�s name in the formula bar and then press Ctrl+A to display the Function Arguments dialog box (or the Select Arguments dialog box, if the function has more than one form). Troubleshooting Why do I get a #NAME? error? There are a few reasons for this error, but one of the more common is typing the function name incorrectly. Here�s a good habit to acquire if you type functions yourself: Use lowercase letters. When you press Enter, Excel converts the name of the function to uppercase letters if you entered it correctly. If the letters don�t change, you probably entered the name of the function incorrectly. If you�re not sure of the exact name or if you continue to get an error, perhaps it�s time to consult Help or use the Insert Function dialog box. Inserting References and Names As with any other formula, you can insert cell references and defined names into your functions easily using the mouse. For example, to enter a function in cell C11 that averages the cells in the range C2:C10, select cell C11, type =average(, and then select the range C2:C10. A marquee appears around the selected cells and a reference to the selected range appears in the formula. Then type the closing parenthesis. If you define named ranges, constants, or formulas in your worksheets, you can insert them into your formulas. To do this, choose Insert, Name, Paste, and then select the name you want in the Paste Name dialog box. When you click OK, the name appears at the insertion point in the formula.