Sliding Commission % when dollar amount spans 2 payout %

[buddyosher]

I am looking for a way to pay a commission rate based on a sliding scale, where the sales amount spans 2 payout %.
(will not allow me to post picture, so I included dropbox link)

and payouts


Quota is $480,000 and it is easy to add up the sales and determine the payout %. What I do not know is to calculate a payout that spans 2 payouts.

[TABLE="width: 564"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]1[/TD]
[TD] $ 480,000[/TD]
[TD] From [/TD]
[TD]From[/TD]
[TD]Available @%[/TD]
[TD]Total Sales[/TD]
[/TR]
[TR]
[TD]3.65%[/TD]
[TD]0%[/TD]
[TD="align: right"]51%[/TD]
[TD] $ - [/TD]
[TD] $ 244,795[/TD]
[TD] $ 735,330[/TD]
[/TR]
[TR]
[TD]6.77%[/TD]
[TD]51%[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 244,800[/TD]
[TD] $ 479,995[/TD]
[TD] Rate % [/TD]
[/TR]
[TR]
[TD]10%[/TD]
[TD]100%[/TD]
[TD="align: right"]150%[/TD]
[TD] $ 480,000[/TD]
[TD] $ 719,995[/TD]
[TD="align: right"]15.00%[/TD]
[/TR]
[TR]
[TD]15%[/TD]
[TD]150%[/TD]
[TD="align: right"]500%[/TD]
[TD] $ 720,000[/TD]
[TD] $ 2,400,000[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
For instance if quota is 480k and sales equal 735,330, is there a formula that would pay the first 244,795 @ 3.65, then 244,800-479,995 @ 6.77% etc?

 

[arthurbr]

Check McGimpsey & Associates : Excel : How differential rates work for differential rates techniques

 

[buddyosher]

Check McGimpsey & Associates : Excel : How differential rates work for differential rates techniques

That is the formula I have to calculate the % payout. My problem is when the dollar splits 2 % payouts... so if I have a total sales of 100 @5% and 150 in sales = 7%... if I sell something for 100, I would get 50 of that at 5% and the other 50 at 7%. Is there a formula that can do that math to split the 100 in new sales half at 5% and half at 7%?

 

[snjpverma]

What does the column C and D of your data contain ? what percentage does that have to do with your output ?
Are you looking to get the total amount of payout ?

for e.g. if the total payout is 2500 and 2900 respectively. then the Final outcome you want should be 5400 ?i.e. addition of both the payouts ?

 

[snjpverma]

Assuming you have 480K in cell F2

Try this formula

=SUMPRODUCT(--(F2>{0;244796;479996;719996}),--(F2-{0;244800;480000;720000}),{0.0365;0.0312;0.0323;0.05})

 

[buddyosher]

If I use this graph
https://www.dropbox.com/s/0mt9g90wtcxunph/graph.jpg?dl=0

C1= quota of 480,000
B= % payout
C&D are ranges of quota ie 0% - 51% = 3.65%
E&F are the $ of those % in sales
G2= Total Sales

and these sales Column K
https://www.dropbox.com/s/qeor601k06fr1kp/sales.jpg?dl=0

so if I am at 200,000 in total sales, payout is 3.65%
if we bring in one sale for 380,000, that would mean total sales would be 200,000 + 380,000 for a total of 580,000 in sales

44,795 would be paid at 3.65%
235195 would be paid at 6.77%
100000 would be paid at 10%

but since the sale is one 380,000 amount, I cannot get a formula to break down payments in those amounts.

Is there a formula that could take one number 380,000 and pay it out in 3 separate % payouts?

 

[snjpverma]

Why is there a gap of 5 dollars in your data? For example after 244795, it directly jumps to 244800. What about 244796, 244797, 244798 & 244799? What percentage do these come under?

 

[buddyosher]

96-99 Falls under the lower %... There are no gaps, it is just an error on my part

 

[snjpverma]

Ok. Got it.
But I wonder why the below formula won't work for you

=SUMPRODUCT(--(G2>{0;244799;479999;719999}),--(G2-{0;244800;480000;720000}),{0.0365;0.0312;0.0323;0.05})

 

[buddyosher]

Ok. Got it.
But I wonder why the below formula won't work for you

=SUMPRODUCT(--(G2>{0;244799;479999;719999}),--(G2-{0;244800;480000;720000}),{0.0365;0.0312;0.0323;0.05})

Thank you, are you able to use cell reference inside { } in any way? if I wanted to use the 3.65% cell B2 and the B2-C2 for the .0312?