Page 754 - Excel 2013 All-in-One For Dummies

736 Creating Custom Excel Functions
such as RVs.) Your custom Commission function will then have two argu- ments — TotalSales and ItemsSold — so that the first line of code on the module sheet in the Code window is
Function Commission(TotalSales,ItemsSold)
In determining how the commissions are actually calculated, suppose that you base the commission percentage on the number of sales made during the month. For five sales or fewer in a month, you pay a commission rate of 4.5 percent of the salesperson’s total monthly sales; for sales of six or more, you pay a commission rate of 5 percent.
To define the formula section of the Commission custom function, you need to set up an IF construction. This IF construction is similar to the IF function that you enter into a worksheet cell except that you use different lines in the macro code for the construction in the custom function. An ELSE command separates the command that is performed if the expression is True from the command that is performed if the expression is False. The macro code is terminated by an END IF command. To set the custom function so that your salespeople get 4.5 percent of total sales for five or fewer items sold and 5 percent of total sales for more than five items sold, you enter the following lines of code underneath the line with the Function command:
If ItemsSold <= 5 Then
Commission = TotalSales * 0.045
Else
Commission = TotalSales * 0.05
End If
Figure 2-6 shows you how the code for this user-defined function appears in the Code window for its module sheets. The indents for the IF...END IF statements are made with the Tab key and make differentiating the parts of the IF construction easy. The first formula, Commission = TotalSales * 0.045 is used when the IF expression ItemsSold <= 5 is found to be True. Otherwise, the second formula underneath the Else command, Commission = TotalSales * 0.05 is used.
After entering the definition for your user-defined function, you are ready
to save it by choosing File➪Save on the Visual Basic Editor menu bar or by pressing Ctrl+S. Then, you can click the View Microsoft Excel button on the Standard toolbar to return to the worksheet where you can try out your new custom function.