Calculate Commission by using vlookup
Posted by Chad on October 17, 2001 12:57 PM
I have a problem I really need help with as soon as possible. This problem involves VLOOKUP!!! I am to enter formulas in the worksheet to calculate the monthly commissions. The user will enter the monthly sales amount in a cell (B7), and your spreadsheet automatically will calculate the commision. The spreadsheet looks like this:
Monthly Sales: (input from user)
Commission Owed = $xx,xxx(formula here)
Commission Table
for Manager
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!!!!
Posted by IML on October 17, 2001 1:09 PM
Let say your table is in H1:J5. You could use
=VLOOKUP(B7,$H$1:$I$5,2)+((B7-VLOOKUP(B7,$H$1:$H$5,1))*VLOOKUP(B7,$H$1:$J$5,3))
good luck
Posted by Aladin Akyurek on October 17, 2001 1:09 PM
Chad,
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.
Aladin
