Chapter 14: Using Functions

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 Microsoft Office Excel 2007 has hundreds of these special functions. Some functions, such as SIN and FACT, are the equivalent of lengthy mathematical formulas you would otherwise have to create by hand. Other functions, such as IF and VLOOKUP, can’t be otherwise duplicated by formulas. When none of the built-in functions is quite what you need, you can create custom functions.

Starting with the section “Understanding Mathematical Functions” on page 461, this chapter describes some of the more useful functions Office Excel 2007 has to offer. To keep this book from threatening the structural integrity of your bookshelf, we’ve had to make some hard choices about which functions to highlight. Therefore, this chapter by no means represents a comprehensive reference. For complete information about all the built-in functions that Office Excel 2007 has to offer, you can use a number of on-screen tools, covered next in “Using the Built-In Function Reference in Excel.”

Using the Built-In Function Reference in Excel

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 Excel Help system includes a detailed description of each worksheet function. Just press F1 to display the Excel Help window, and then type a function name in the Search text box to find all the relevant Help topics. You can also click Function Reference in the Table Of Contents, where the functions are grouped into categories to help you find the one you need. For example, clicking the Logical category and then the Logical Functions topic displays the information shown in Figure 14–1.

  
  Figure 14–1: The online Help system includes a comprehensive function reference.

• The Insert Function dialog box   You can use this dialog box, shown in Figure 14–2, to browse through the entire list of functions if you’re not sure which function you need. To display the Insert Function dialog box, click the Insert Function button on the formula bar.

  
  Figure 14–2: The Insert Function dialog box provides assistance with using functions.

• The Function Arguments dialog box   This dialog box, shown in Figure 14–3, provides details about the function, and the required arguments appear as separate text boxes in the middle of the dialog box. Notice also the link to the relevant Help topic at the bottom of the dialog box. To display the Function Arguments dialog box, click the Insert Function button on the formula bar, select a function, and click OK. You can also click the Insert Function button while you are in the process of entering a formula after you type a valid function name and an open parenthesis to display the Function Arguments dialog box.

  
  Figure 14–3: The Function Arguments dialog box provides assistance with entering function arguments.

  Note 

  Drag the Function Arguments dialog box around the screen if you need to see the cells behind it. For maximum viewing, make the dialog box smaller by clicking one of the collapse dialog buttons on the right side of the argument boxes.

• Function ScreenTips   These little pop-up descriptions that appear below selected formulas are useful if you are unsure about the syntax of a function as you type a formula; 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 14–4. The ScreenTip shows you the correct function syntax and any available alternate versions of the function (also shown in Figure 14–4). You can also 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 14–4.

  
  Figure 14–4: Click an argument name in the Function ScreenTip, which appears when you click an existing function, to highlight the corresponding argument in the cell.

  Note 

  To turn off Function ScreenTips, click the Microsoft Office Button, click Excel Options, select the Advanced category, and then in the Display area, clear the Show Function ScreenTips check box.

• Formula AutoComplete   As you type a formula, Excel provides pop-up lists that offer function names and defined names that match the letters you are typing in the formula. For example, if you type =S in a cell, Excel displays a scrolling list of all functions (and defined names, if any) that begin with the letter S that you can then double-click to insert into your formula.

For details, see “Using Formula AutoComplete” on page 406 and “Naming Cells and Cell Ranges” on page 407.