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.

[TABLE="class: head"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

1​

[/TD]
[TD]

Over...​

[/TD]
[TD]

Rate​

[/TD]
[TD]

Diff Rate​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

2​

[/TD]
[TD]

0​

[/TD]
[TD]

10%​

[/TD]
[TD]

10%​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

3​

[/TD]
[TD]

40000​

[/TD]
[TD]

12%​

[/TD]
[TD]

2%​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

4​

[/TD]
[TD]

50000​

[/TD]
[TD]

15%​

[/TD]
[TD]

3%​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

5​

[/TD]
[TD]

60000​

[/TD]
[TD]

20%​

[/TD]
[TD]

5%​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

6​

[/TD]
[TD]

70000​

[/TD]
[TD]

25%​

[/TD]
[TD]

5%​

[/TD]
[/TR]
</tbody>[/TABLE]

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!