vlook up or IF ?

screenplay greg

New Member
Joined
Oct 27, 2002
Messages
8
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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

screenplay greg

New Member
Joined
Oct 27, 2002
Messages
8
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.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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.

paddy
 

screenplay greg

New Member
Joined
Oct 27, 2002
Messages
8

ADVERTISEMENT

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
 

screenplay greg

New Member
Joined
Oct 27, 2002
Messages
8
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
 

screenplay greg

New Member
Joined
Oct 27, 2002
Messages
8

ADVERTISEMENT

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
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,976
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

gopa

New Member
Joined
Oct 23, 2002
Messages
32
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)%
 

Forum statistics

Threads
1,144,769
Messages
5,726,183
Members
422,661
Latest member
foxleinlady

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?

Disable AdBlock

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
Top