Back to Forms in Excel VBA archive index

Back to archive home

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

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

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.

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.