A function with one argument

A single-argument function 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 21-1.

Table 21-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<=9999.99),A1*0.08,IF(AND(A1>=10000,A1<=19999 .99),A1*0.105,IF(AND(A1>=20000,A1<=39999.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 * Ti

er2

Case 20000 To 39999.99: Commission =

Sales * Ti

er3

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(25000)

Figure 21-1 shows a worksheet that uses this new function.

Figure 21-1:

Using the Commission function in a worksheet.

Figure 21-1:

Using the Commission function in a worksheet.

l-¿¿] Microsoft Excel - custom functions.xls

a â\m

: File Edit View insert Format loo Is Data Window Help

: J J J J 1 J a 1 V fi 1 t ^ a - J M - ' 1A E - Il mm 4 too*.

B X 't

flrial - m . I B I a

U1LS % ' ?P5 i™ I

1FÍFIL

i^iA'a

C2 jf, fe =commission(B2)

mui s m

D

E l e

e h

a

1

Hiriie

Sales Commissinn

2

Adams

feS93.00| $8,677J62

3

BatiÉ

MIMffW (280.4B

4

Bouqlas

:(38':973.00 S4.67E..7B

S

Fmrrrett

(32,092.00 (3,851 04

6

FrankEin

(27:354.00 (iï8ï:i8

7

Johnsorr

(17-,333.00 (1,872 43

8

Kb tit":

(41,598.00 (5:,823 72

9

Ï32

(3,840 00

10

Quincy

(5,000.00

(400 00

11

Randall

(88;.793:00

(9 £31 02

■:.

Smith

131,093.00

(3,731.IB

w

Walker

(24:609:00

(2,941.03

14

Zeller

(41,544.00 (5,816.1B

15

v*

n k

► >f[\ Sheetl\Shset2/i

l< „,, i

>

A function with two arguments

The next example builds on the preceding one. Imagine that the sales manager implements a new policy: The total commission paid increases by 1 percent for every year the salesperson has been with the company.

I modified the custom Commission function (defined in the preceding section) so that it takes two arguments, both of which are required arguments. Call this new function Commission2:

Function Commission2(Sales, Years)

' Calculates sales commissions based on years in service 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: Commission2 = Sales * Tierl Case 10000 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)

Commission2 =

Round(Commission2, 2)

End Function

I simply added the second argument (Years) to the Function statement and included an additional computation that adjusts the commission before exiting the function. This additional computation multiplies the original commission by the number of years in services, divides by 100, and then adds the result to the original computation.

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

0 0

Post a comment