# vlook up or IF ?

#### screenplay greg

##### New Member
I'm trying to create a spreadsheet to calculate commissions based on a cumulative sales amount. The commission % is based on a sliding scale. When the cumulative gets over a amount then the commission rate changes.
I'm having trouble figuring out how to utilize the IF or Vlookup functions.
For example if the cumulative commissions go from 100 to 300 in one month and the commission changes at the 200 point how can I structure an argument to make sure that the portion from 101-200 gets calculated at one rate while the amount over 200 gets calculated at the new rate ?

I hope this makes sense.

Thanks

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Thanks for the reply - I read that article you referred me to, but I'm still not sure how to take care of the cumulative part of what I'm trying to do.
The formula has to be able to ;
1 - look at the amount of cumulative sales to date in a cell and determine which commission rate applies
2 - If two rates apply then the formula must look at the previous cumulative amount and subtract that from the first rate limit and then take everything above that limit and apply the next rate.

For example if the rate changes from 5% to 10% at the \$400 point and the cumulative sales went from 300 the previous month to 500 this month it needs to be able to apply 5% to \$100 and 10% to the other \$100.

please post up a snapshot of your data using the html maker addin available from the link below this post. Provide sample data & an indication of the expected result.

Apparently my machine won't accept plug-in right now so here it goes:

Commission % Revenue level(annual)
2.5% 0-400,000
19% 400,000-1,000,000
20% 1,000,000+

The sales are tracked cumulatively throughout the year and if ,for example, the cumulative revenue booked went up from 300,000 in month 3 to 500,000 in month 4 then the function should calculate the commission on the 200,000 of new revenue as follows: 100,000 @ 2.5% and 100,000 @ 19% for a total commission for that month of 21,500. The tricky part for me is considering the cumulative while calculating the commission on this month's booked revenue particularly when the commission level changes.

I hope that is clear and thanks so much for your responses.

Greg

Apparently my machine won't accept plug-in right now so here it goes:

Commission % Revenue level(annual)
2.5% 0-400,000
19% 400,000-1,000,000
20% 1,000,000+

The sales are tracked cumulatively throughout the year and if ,for example, the cumulative revenue booked went up from 300,000 in month 3 to 500,000 in month 4 then the function should calculate the commission on the 200,000 of new revenue as follows: 100,000 @ 2.5% and 100,000 @ 19% for a total commission for that month of 21,500. The tricky part for me is considering the cumulative while calculating the commission on this month's booked revenue particularly when the commission level changes.

I hope that is clear and thanks so much for your responses.

Greg

Apparently my machine won't accept plug-in right now so here it goes:

Commission % Revenue level(annual)
2.5% 0-400,000
19% 400,000-1,000,000
20% 1,000,000+

The sales are tracked cumulatively throughout the year and if ,for example, the cumulative revenue booked went up from 300,000 in month 3 to 500,000 in month 4 then the function should calculate the commission on the 200,000 of new revenue as follows: 100,000 @ 2.5% and 100,000 @ 19% for a total commission for that month of 21,500. The tricky part for me is considering the cumulative while calculating the commission on this month's booked revenue particularly when the commission level changes.

I hope that is clear and thanks so much for your responses.

Greg

You can use various formulas such as SumProduct; Vlookup provides a good solution.

With a table of commission rates that I named
for conveniene rComm

0...... 0.025 0.00
400,000.. 0.19 10,000
1,000,000 0.2 124,000
10,000,000

The formula below will calculate the total commission on the accumulated amount.

=(A2-VLOOKUP(A2,rComm,1))*VLOOKUP(A2,rComm,2)+VLOOKUP(A2,rComm,3)

Use a formula like the above for month 2
and a similar formula for month 1,
the difference is the commission for the incremental amount.
This message was edited by dave patton on 2002-10-29 15:49

Dave,

Thanks so much - that did it.

Another method is to apply the bottom rate to all sales, then subtract the first rate bracket amount from the total sales and apply the difference between that bracket rate and the first rate, etc. etc.

Sales 600 rate to 200 @ 5%, to 400 @ 6%, above 400 @7%

Total = 600*5% + (600-200)*(6-5)% + (600-400)*(7-6)%

Replies
6
Views
879
Replies
3
Views
152
Replies
11
Views
1K
Replies
5
Views
273
Replies
5
Views
333

1,196,485
Messages
6,015,472
Members
441,898
Latest member
kofafa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back