# A function with one argument

The single-argument function in this section is designed for sales managers who need to calculate the commissions earned by their salespeople. The commission rate depends on the monthly sales volume; those who sell more earn a higher commission rate. The function returns the commission amount based on the monthly sales (which is the function's only argument — a required argument). The calculations in this example are based on Table 20-1.

 Table 20-1 Commission Rates by Sales Monthly Sales Commission Rate 80-89,999 8.0% 810,000-819,999 10.5% 820,000-839,999 12.0% 840,000+ 14.0%

You can use several approaches to calculate commissions for sales amounts entered into a worksheet. You could write a lengthy worksheet formula such as this:

=IF(AND(A1>=0,A1<=99 99.9 9),A1*0.0 8<IF(AND(A1>=100 00< A1<=19999.99),A1*0.105,IF(AND(A1>=20000,A1<=3 9999.99), A1*0.12,IF(A1>=40000,A1*0.14,0))))

A couple reasons make this a bad approach. First, the formula is overly complex. Second, the values are hard-coded into the formula, making the formula difficult to modify if the commission structure changes.

A better approach is to create a table of commission values and use a LOOKUP table function to compute the commissions:

=VLOOKUP(A1,Table,2)*A1

Another approach, which doesn't require a table of commissions, is to create a custom function:

 Function Commission(Sales) ' Calculates sales commissions Dim Tierl As Double, Tier2 As Double Dim Tier3 As Double, Tier4 As Double Tierl = 0.08 Tier2 = 0.105 Tier3 = 0.12 Tier4 = 0.14 Select Case Sales Case 0 To 9999.99: Commission = Sales * Tierl Case 10000 To 19999.99: Commission = Sales * Tier2 Case 20000 To 39999.99: Commission = Sales * Tier3 Case Is >= 40000: Commission = Sales * Tier4 End Select Commission = Round(Commission, 2) End Function

After you define this function in a VBA module, you can use it in a worksheet formula. Entering the following formula into a cell produces a result of 3,000. The amount of 25000 qualifies for a commission rate of 12 percent:

=Commission(2500 0)

Figure 20-1 shows a worksheet that uses the Commission function.

 - I c ■■Nrimp Sales_I Commission