Microsoft Office Tutorials and References
In Depth Information
Calculating Sales Commissions
This function is very easy to understand and maintain. It uses constants to store the commission
rates as well as a Select Case structure to determine which commission rate to use.
When a Select Case structure is evaluated, program control exits the Select Case
structure when the first true Case is encountered.
A function for a more complex commission structure
If the commission structure is more complex, you may need to use additional arguments for your
COMMISSION function. Imagine that the aforementioned sales manager implements a new policy
to help reduce turnover: The total commission paid increases by 1 percent for each year that a
salesperson stays with the company.
The following is a modified COMMISSION function (named COMMISSION2 ). This function now
takes two arguments: the monthly sales (sales) and the number of years employed (years).
Function COMMISSION2(Sales As Double, Years As Long) As Double
‘ Calculates sales commissions based on
‘ years in service
Const Tier1 As Double = 0.08
Const Tier2 As Double = 0.105
Const Tier3 As Double = 0.12
Const Tier4 As Double = 0.14
Select Case Sales
Case Is >= 40000
COMMISSION2 = Sales * Tier4
Case Is >= 20000
COMMISSION2 = Sales * Tier3
Case Is >= 10000
COMMISSION2 = Sales * Tier2
Case Is < 10000
COMMISSION2 = Sales * Tier1
COMMISSION2 = COMMISSION2 + (COMMISSION2 * Years / 100)
Figure 25-3 shows the COMMISSION2 function in use. The formula in cell D2 is
The workbook, commission function.xlsm , shown in Figure 25-3, is available on
the companion CD-ROM.