Hi All
Slab 0-25 25-50 50-100 >101
Incentive 10 20 30 40
I have above slab and incentive Structure.
I have different person who will sell different quantities.
Supposingly if i have one person whos sold 120, the following steps i follow to arrive at incentive.
First 25 units, 25*10$
25-50 units, 25*20$
50-100 units , 50*30$
>100, 19*40
Total Result 3010
How to formulate this.
Any help will be greatly appreciated.
Thanks
Google Tiered commission or have a look here for similar
http://www.mrexcel.com/forum/excel-q...formula-2.html
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
I think that solution should actually be 3050, as the last step would be 20*40. Here is a formula that gets that result:Here is a file that shows a possible setup, the slabs are set up in A2:B5 and the incentives are in C2:C5 Bin Based Calculations.xlsx
=SUM(MIN(A8*C2,B2*C2),MAX(0,MIN((A8-B2)*C3,(B3-B2)*C3)),MAX(0,MIN((A8-B3)*C4,(B4-B3)*C4)),MAX(0,(A8-B4)*C5))
Let me know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
I also think that the answer to the question posed should by 3050 after manually verifying my results. Enter this where you need the result given that the value entered is in B1.
Formula:
=IF(B1<=25,B1*10,IF(B1<=50,(B1-25)*20+250,IF(B1<=100,(B1-50)*30+750,IF(B1>100,(B1-100)*40+2250))))
The values 250, 750, 2250 are the totals as the range limits are passed. Eg if the value is 30 then the limit for the first range (0-25) has been passed and the value accumulated is 250. To this is added the amount in the next range etc.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks