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.

1	$ 480,000	From	From	Available @%	Total Sales
3.65%	0%	51%	$ -	$ 244,795	$ 735,330
6.77%	51%	100%	$ 244,800	$ 479,995	Rate %
10%	100%	150%	$ 480,000	$ 719,995	15.00%
15%	150%	500%	$ 720,000	$ 2,400,000

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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?