Monthly Sales: (input from user)
Commission Owed = $xx,xxx(formula here)
Monthly Base Add'l Commission
Sales Commission Rate
$0 $0 5%
$50,000 $1,500 8%
$75,000 $2,000 10%
$100,000 $2,500 12%
$120,000 $3,000 14%
Example: Sales of $152,000
Sales Account Manager
$3,000 + [($152,000 - $120,000) X 14%]
I know that I will need two vlookup statement in this. Please help!!!!
|Check out our Excel Resources|
Let say your table is in H1:J5. You could use
Select the table of commisssions excluding labels such as Sales, etc. Go to the Name Box on the Formula Bar and type CommisionTable.
Assuming that input from user is A1,
in A2 enter: =vlookup(a1,CommissionTable,2)+(a1-vlookup(a1,CommissionTable,2))*(vlookup(a1,CommissionTable,3))
If you prefer (I would),
in A2 enter: =vlookup(a1,CommissionTable,2)
in A3 enter: =a2+(a1-a2)*(vlookup(a1,CommissionTable,3))
to avoid looking up the same thing twice.