Microsoft Office Tutorials and References
In Depth Information
Calculating Sales Commissions
You can calculate commissions for various sales amounts entered into a worksheet in several
ways. You can use a complex formula with nested IF functions, such as the following:
=IF(A1<0,0,IF(A1<10000,A1*0.08,
IF(A1<20000,A1*0.105,
IF(A1<40000,A1*0.12,A1*0.14))))
This may not be the best approach for a couple of reasons. First, the formula is overly complex,
thus making it difficult to understand. Second, the values are hard-coded into the formula, thus
making the formula difficult to modify.
A better approach is to use a lookup table function to compute the commissions. For example:
=VLOOKUP(A1,Table,2)*A1
Using VLOOKUP is a good alternative, but it may not work if the commission structure is more
complex. (See the “A function for a simple commission structure” section for more information.)
Yet another approach is to create a custom function.
A function for a simple commission structure
The following COMMISSION function accepts a single argument (sales) and computes the
commission amount:
Function COMMISSION(Sales As Double) As Double
‘ Calculates sales commissions
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
COMMISSION = Sales * Tier4
Case Is >= 20000
COMMISSION = Sales * Tier3
Case Is >= 10000
COMMISSION = Sales * Tier2
Case Is < 10000
COMMISSION = Sales * Tier1
End Select
End Function
The following worksheet formula, for example, returns 3,000 (the sales amount — 25,000 —
qualifies for a commission rate of 12 percent):
 
Search JabSto ::




Custom Search