A function with two arguments

Imagine that the aforementioned hypothetical sales managers implement a new policy to help reduce turnover: The total commission paid is increased by 1 percent for every year that the salesperson has been with the company.

I modified the custom Commission function (defined in the preceding section) so that it takes two arguments. The new argument represents the number of years. Call this new function Commission2:

Function Commission2(Sales, Years) ' Calculates sales commissions based on ' years in service

Const Tier1 = 0.08 Const Tier2 = 0.105 Const Tier3 = 0.12 Const Tier4 = 0.14 Select Case Sales

Case 0 To 9999.99: Commission2 = Sales * Tier1 Case 1000 To 19999.99: Commission2 = Sales * Tier2 Case 20000 To 39999.99: Commission2 = Sales * Tier3 Case Is >= 40000: Commission2 = Sales * Tier4 End Select

Commission2 = Commission2 + (Commission2 * Years / 100) End Function

Pretty simple, eh? I just added the second argument (Years) to the Function statement and included an additional computation that adjusts the commission.

Here's an example of how you can write a formula using this function (it assumes that the sales amount is in cell A1 and the number of years the salesperson has worked is in cell B1):

=Commission2(A1,B1)

CD- All of these commission-related procedures are available on the companion CD-ROM in a ROM file named ® commission functions.xlsm.

0 0

Post a comment