01/28/13

How to build a function

If you can use a mouse, you can build a function
Here's the mistake most people make when it comes to learning how to use an Excel function. They go to Help | Contents And Index, click the Index tab, and locate the function name in the list. They print the help text and then try to manually compose the function using the printout as a guide.

There's nothing wrong with this approach. In fact, the help text contains many valuable examples of the functions at work. But for some users, Excel's Paste Function feature provides an easier way to enter formulas.

In general, here's how you would use the Paste Function feature:

1. Click the cell where you want to enter the formula.
2. Click the Paste Function icon on the Standard toolbar or go to Insert | Function.
3. In the Paste Function dialog box, select the appropriate function category and then choose the function from the Function Name list. Excel will display a description of the function, including its arguments.
4. Click OK, and the Function Wizard will display fields in which you enter the appropriate values or cell references for the function's arguments. (You can key those entries directly, or you can use the mouse to select them.)

Building an IF function with the mouse
Let's use the raw data in Figure A to demonstrate how this feature works. Suppose you want to compose an IF function (or "IF test") that displays the word "Excellent" if the value in column B is at least 600, and you want to display the word "Good" if the value is less than 600.

Begin by selecting cell C2 and choosing Insert | Function. In the Function Category list, select Logical. Then select IF from the Function Name list. When you do, Excel will display a description and the arguments of the IF function, as shown in Figure B.

Click OK, and Excel launches a special window that contains fields for each of the IF function's arguments, as shown in Figure C. You can manually enter the arguments in these fields, or you can click the red arrow at the right-hand edge of a field and navigate to the cell or range of cells you need for your function.

Figure D shows what our screen looks like after we clicked the red arrow for the Logical_test field and clicked once on cell B2. Once you've selected the cell (or range of cells), click the red arrow again to return to the function-building window.

Next, type <600 to complete the rest of your Logical_test field, and press [Tab] to move to the Value_if_true field. Type Good, press [Tab] to move to the Value_if_false field, and type Excellent. When you move out of the field, you'll notice that Excel encloses the word Good in quotation marks, as shown in Figure E.

Click OK to enter your function in the worksheet. Then, simply copy that cell down the appropriate number of rows. Figure F shows our final result.

Copyright © 1999-2001 TechRepublic, Inc.

Visit us at www.TechRepublic.com

Created Date: 10/17/2001  Last Reviewed: 10/17/2001  Rev. Date: