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?

 

[snjpverma]

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?

I have already used that in the last part of my formula inside { }
isn't the below numbers hardcoded in my formula ?
{0.0365;0.0312;0.0323;0.05}

 

[buddyosher]

here is the spreadsheet
https://www.dropbox.com/s/g9rwm48g1ze98yy/SampleComp.xlsx?dl=0

 

[buddyosher]

I have already used that in the last part of my formula inside { }
isn't the below numbers hardcoded in my formula ?
{0.0365;0.0312;0.0323;0.05}

Yeah, those are the right numbers, I wanted to use cells so I could copy to other quarters with different numbers and populate automatically without rewrite the code

 

[snjpverma]

ohh ok. in this case, you can certainly use the ranges instead of hard-coding the numbers. However, don't use { } these brackets when you are referring to ranges.

For e.g. instead of --(G2>{0;244799;479999;719999})
You will have to write it like this

--(G2>F2:F6) -- just assuming that the data is in F2:F6

I hope this helps

 

[buddyosher]

ohh ok. in this case, you can certainly use the ranges instead of hard-coding the numbers. However, don't use { } these brackets when you are referring to ranges.

For e.g. instead of --(G2>{0;244799;479999;719999})
You will have to write it like this

--(G2>F2:F6) -- just assuming that the data is in F2:F6

I hope this helps

=SUMPRODUCT(--(G2>0,F2:F5),--(G2-(0,F2:F5)),B2;(B3-B2);(B4-B3);(B5-B4)

G2 is the sales figure
F2:F5 is the top of the quota range
B2-B5 is 3.65%;6.77%;10%;15%

but that does not work?

 

[snjpverma]

You will have to select the bottom quota range, not top.
Secondly, avoid the below highlighted zeros

=SUMPRODUCT(--(G2>0,F2:F5),--(G2-(0,F2:F5)),B2;(B3-B2);(B4-B3);(B5-B4)

Lastly, don't write the subtractions in this way. The array won't work for a range like this. Instead you can use a helper column . for e.g. in column H and use the below formula.
I have assumed here that your Min quota range is in column E

=SUMPRODUCT(--(G2>E2:E5),--(G2-E2:E5),H2:H5)