Microsoft Office Tutorials and References
In Depth Information
Note that the commission rate is nonlinear and also depends on the month’s total sales.
Employees who sell more earn a higher commission rate.
You can calculate commissions for various sales amounts entered into a worksheet in several
ways. If you’re not thinking too clearly, you can waste lots of time and come up with a lengthy
formula such as this one:
This approach is bad for a couple of reasons. First, the formula is overly complex, making it
difficult to understand. Second, the values are hard-coded into the formula, making the formula
difficult to modify.
A better (non-VBA) approach is to use a lookup table function to compute the commissions. For
example, the following formula uses VLOOKUP to retrieve the commission value from a range
named Table and multiplies that value by the value in cell A1.
Yet another approach (which eliminates the need to use a lookup table) is to create a custom
function such as the following:
Const Tier1 = 0.08
Const Tier2 = 0.105
Const Tier3 = 0.12
Const Tier4 = 0.14
‘ Calculates sales commissions
Select Case Sales
Case 0 To 9999.99: Commission = Sales * Tier1
Case 1000 To 19999.99: Commission = Sales * Tier2
Case 20000 To 39999.99: Commission = Sales * Tier3
Case Is >= 40000: Commission = Sales * Tier4
After you enter this function in a VBA module, you can use it in a worksheet formula or call the
function from other VBA procedures.
Entering the following formula into a cell produces a result of 3,000; the amount — 25,000 —
qualifies for a commission rate of 12 percent: