Calculating a tiered commission

[ckoehl]

I have tried and tried many ways and cannot get a formula to work. PLEASE HELP!
I am setting up a spreadsheet to calculate sales commissions. The tiers are:
$0-$40,000 @ 50/50 $40,001-50,000 @ 60/40 $50,001-$60,000 @ 70/30 $60,001-$70,000 @80/20 $70,001 & up @ 90%
It is cumulative for the year

Here are my columns if that helps with how I need to lay it out:
Gross Commission YTD Gross Emp Commission Company side


Hope that makes sense! Thanks!

 

[joeu2004]

I do not understand your notation for commission rates (50/50, 60/40, etc). And a commission rate of 90% of the amount over 70,000 seems insane. That cannot possibly be what you mean.

The following is a paradigm that you might be able to customize with the correct commission rates.

	A​	B​	C​
1​	Over...​	Rate​	Diff Rate​
2​	0​	10%​	10%​
3​	40000​	12%​	2%​
4​	50000​	15%​	3%​
5​	60000​	20%​	5%​
6​	70000​	25%​	5%​

<tbody>
</tbody>

The formula in C2 is =B2-N(B1) . Copy C2 into C3:C6.

Then with the sales amount in S1, the commission is:

=SUMPRODUCT((S1>$A$2:$A$6)*(S1-$A$2:$A$6), $C$2:$C$6)

formatted as Currency.

 

[ckoehl]

Thanks for your help Joe, yes, I posted an example of a commission structure that we offer. Maybe you misunderstood our basis, once they hit 70k in gross closed commission, they make 90% of additional commission splits. I will try the formula though!