Calculating a tiered commission

ckoehl

New Member
Joined
Dec 10, 2018
Messages
2
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,515
Office Version
2010
Platform
Windows
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.
 
Last edited:

ckoehl

New Member
Joined
Dec 10, 2018
Messages
2
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!
 

Forum statistics

Threads
1,077,649
Messages
5,335,473
Members
399,017
Latest member
npatel917

Some videos you may like

This Week's Hot Topics

Top