Tiered Commission Formual for a group of people

[pixie222]

Hi, I need to calculate the commission of my team, they get commission on credit card payments that they take. The tiers are:
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto -0.65pt; WIDTH: 217pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 0in 0in 0in" cellSpacing=0 cellPadding=0 width=289 border=0><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 217pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt" width=289 colSpan=2>

Commission Tiers<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e8bfff; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 125pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt" width=167>

$0 - $10,000 <o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e8bfff; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0in; BORDER-LEFT: #e8bfff; WIDTH: 92pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt" width=123>

3%<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e8bfff; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 125pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt" width=167>

$10,000 - $15,000 <o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e8bfff; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0in; BORDER-LEFT: #e8bfff; WIDTH: 92pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt" width=123>

4%<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e8bfff; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 125pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt" width=167>

$15,000 - $20,000 <o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e8bfff; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0in; BORDER-LEFT: #e8bfff; WIDTH: 92pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt" width=123>

5%<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e8bfff; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 125pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt" width=167>

$20,000 - $25,000 <o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e8bfff; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0in; BORDER-LEFT: #e8bfff; WIDTH: 92pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt" width=123>

6%<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e8bfff; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 125pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt" width=167>

$25,000 - $30,000 <o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e8bfff; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0in; BORDER-LEFT: #e8bfff; WIDTH: 92pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt" width=123>

7%<o></o>​

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 6; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e8bfff; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 125pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt" width=167>

$30,000 + <o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e8bfff; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0in; BORDER-LEFT: #e8bfff; WIDTH: 92pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12.75pt" width=123>

8%<o></o>​

</TD></TR></TBODY></TABLE>

I have about 30 people to track this for and am having a hard time figuring out the proper formulas. Can you help me please?

 

[Dave Patton]

Can you give an example to clarify the calculation that you require?

 

[GlennUK]

Look at this ( 4th or 5th post down ):

http://www.tek-tips.com/viewthread.cfm?qid=1282681&page=1

 

[pixie222]

Can you give an example to clarify the calculation that you require?

Yep, I have a table with everyone's names in a column and the months in a row, I need to know what everyone makes each month.

If sally collected $22,000 and Ted collected $28,500, what would be their commission based on the tiers?

 

[Domenic]

Assuming that A2 contains the amount collected, try...

=SUMPRODUCT(--(A2>{0,10000,15000,20000,25000,30000}),A2-{0,10000,15000,20000,25000,30000},{0.03,0.01,0.01,0.01,0.01,0.01})

This method is described here...

Hope this helps!

xl-central.com

 

[pixie222]

Assuming that A2 contains the amount collected, try...

=SUMPRODUCT(--(A2>{0,10000,15000,20000,25000,30000}),A2-{0,10000,15000,20000,25000,30000},{0.03,0.01,0.01,0.01,0.01,0.01})

This method is described here...

Hope this helps!

xl-central.com

Works like a charm Thank you!

 

[GlennUK]

Yep, I have a table with everyone's names in a column and the months in a row, I need to know what everyone makes each month.

If sally collected $22,000 and Ted collected $28,500, what would be their commission based on the tiers?

So you wouldn't have the first $10000 of Sally's ( $22,000 ) on 3%, and the next $5000 on 4%, the next $5000 on 5% and the final $2000 on 6% then, but instead have the commission amount as being 6% of $22,000?

EDIT: OK, it looks like you would like it that way, and are using Domenics method ( mine works the same way too ).