Developing Custom Functions in Excel

Formulas are the lifeblood of Excel. You can't find too many worksheets that are devoid of formulas because you need formulas to figure out new values based on your existing data. Microsoft includes a wealth of standard formulas in Excel. In fact, it's possible that you'll never need anything more than the Microsoft formulas. However, formulas are extremely important, so it's handy to know how to create one of your own.

All formulas in Excel rely on functions. If you want to create a special formula for your worksheet, all you need is a function to perform the task. The functions that you create appear in the User Defined category of the Insert Function dialog box, which you access by using the following procedure:

1. Select the Formulas tab of the Ribbon.

2. Click Function Wizard.

You see the Insert Function dialog box, shown in Figure 14-7.

Insert Function Search for a function:

Type a brief description of what you want to do and then dick Go]

Or select a category: User Defined Select a function:

Figure 14-7:

Choosing a user-defined function to insert in a worksheet.

Type a brief description of what you want to do and then dick Go]

Or select a category: User Defined Select a function:

GetLastSheet

Pythagoras

ConvertString(Original3

This furit Uün reverses the characters in a string.

ConvertString(Original3

This furit Uün reverses the characters in a string.

Help on this function

3. Select User Defined in the Or Select a Category field.

4. Highlight the function you want to insert.

You might see the optional Function Arguments dialog box, shown in Figure 14-8 (this one is for the Pythagoras function described in the "Defining math calculations" section, later in this chapter). Notice how the dialog box shows the result of the calculation, which means that you could use this dialog box to perform a what-if analysis without ever inserting the function. Simply click Cancel when you finish working with the function.

Figure 14-8:

Add any required arguments for the function you select.

Figure 14-8:

Add any required arguments for the function you select.

6. Type the arguments required for the function, and then click OK.

Excel inserts the function into the current cell.

Was this article helpful?

0 0

Post a comment