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

Was this article helpful?

0 0

Post a comment