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.


Figure A

We'll use the Paste Function feature to create an IF test that displays different results, depending on the values in column B.

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.


Figure B

The Paste Function dialog box displays a description and the arguments of the function you select.

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 C

This window makes it easy to enter the arguments 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.


Figure D

Click the red arrow beside a field when you want to select a range with your mouse instead of typing it manually.

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.


Figure E

Here's what our IF function looks like after we've filled in each of the three arguments.

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.


Figure F

After we created the function visually, we copied that formula into the other cells in column C.


Keep the Excel tips coming!

Get valuable tips for using worksheet functions, VBA code, and much more, all delivered straight to your inbox. Best of all, it's absolutely free. Sign up for the Excel TechMail today!

Copyright 1999-2001 TechRepublic, Inc.

Visit us at www.TechRepublic.com


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

This site was last updated 07/20/08