Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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!!!!


Check out our Excel Resources

Re: Calculate Commission by using vlookup

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


Re: Calculate Commission by using vlookup

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.