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?

 

[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)