Microsoft Office Tutorials and References
In Depth Information
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):
=COMMISSION(25000)
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.
Search JabSto ::

Custom Search