Sales Commission Sliding Scale Calculator

[buddyosher]

A B
1 Name Bookings
2 Integis $10,500
3 ABC Company $17,500
4 Today Company $30,000
5 anuary $58,000

Booking Scale Commission payout
$15,000.00 10%
$36,000.00 15%
$45,000.00 20%
$56,250.00 25%


How would I get progressive payout to work. So up to 15k I pay 10% so B2 price of $10,500 is paid at 10%, B3 is 17,500 and that would mean $4,500 would be paid at 10% and the balance or $13,000 would be paid at 15%.. and so on and so forth.

Is there a template for this move? If so any help would be great!

Jon

 

[xlmaniac]

Hi Jon,
I am putting forward a solution based on my understanding of your problem.
Pls check whether it is yielding the desired result what you want or not.

Excel Workbook
	A	B	C
1	Name	Bookings	Commission
2	Integis	$10,500	1500
3	ABC company	$17,500	1875
4	Today company	$30,000	3750
5	anuary	$58,000	12250
Sheet1

 

[clicker]

Hi Jon,
see the youtube video:
http://www.youtube.com/watch?v=fvSnFEsn_NI

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Name</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Bookings</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Commission</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Integis</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">$10,500 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">$1,050.00 </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">ABC company</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">$17,500 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">$1,875.00 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Today company</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">$30,000 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">$3,750.00 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">anuary</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">$58,000 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">$9,787.50 </td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=B2*10%+MAX(<font color="Blue">0,$B2-15000</font>)*5%+MAX(<font color="Blue">0,$B2-36000</font>)*5%+MAX(<font color="Blue">0,B2-45000</font>)*5%+MAX(<font color="Blue">0,B2-56250</font>)*5%</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=B3*10%+MAX(<font color="Blue">0,$B3-15000</font>)*5%+MAX(<font color="Blue">0,$B3-36000</font>)*5%+MAX(<font color="Blue">0,B3-45000</font>)*5%+MAX(<font color="Blue">0,B3-56250</font>)*5%</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=B4*10%+MAX(<font color="Blue">0,$B4-15000</font>)*5%+MAX(<font color="Blue">0,$B4-36000</font>)*5%+MAX(<font color="Blue">0,B4-45000</font>)*5%+MAX(<font color="Blue">0,B4-56250</font>)*5%</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=B5*10%+MAX(<font color="Blue">0,$B5-15000</font>)*5%+MAX(<font color="Blue">0,$B5-36000</font>)*5%+MAX(<font color="Blue">0,B5-45000</font>)*5%+MAX(<font color="Blue">0,B5-56250</font>)*5%</td></tr></tbody></table></td></tr></table><br />

 

[Firefly2012]

A B
1 Name Bookings
2 Integis $10,500
3 ABC Company $17,500
4 Today Company $30,000
5 anuary $58,000

Booking Scale Commission payout
$15,000.00 10%
$36,000.00 15%
$45,000.00 20%
$56,250.00 25%


How would I get progressive payout to work. So up to 15k I pay 10% so B2 price of $10,500 is paid at 10%, B3 is 17,500 and that would mean $4,500 would be paid at 10% and the balance or $13,000 would be paid at 15%.. and so on and so forth.

Is there a template for this move? If so any help would be great!

Jon

You want to do it on a cumulative basis, so assuming I am correct, you want this:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td width="25px" style="color: #161120;text-align: center;">1</td><td width="63px" style="font-weight: bold;;">Name</td><td width="63px" style="font-weight: bold;;">Bookings</td><td width="63px" style="font-weight: bold;;">Commission</td><td width="63px" style="font-weight: bold;text-align: right;;"></td><td width="63px" style="font-weight: bold;text-align: right;;"></td><td width="63px" style="font-weight: bold;;">Commission</td><td width="63px" style="font-weight: bold;text-align: right;;"></td><td width="63px" style="font-weight: bold;text-align: right;;"></td><td width="63px" style="font-weight: bold;text-align: right;;"></td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">2</td><td width="63px" style=";">Integis</td><td width="63px" style="text-align: right;;">$10,500</td><td width="63px" style="text-align: right;;">1050</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="font-weight: bold;;">Lower limit</td><td width="63px" style="font-weight: bold;;">Upper limit</td><td width="63px" style="font-weight: bold;;">Rate</td><td width="63px" style="font-weight: bold;;">Differential Rate</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">3</td><td width="63px" style=";">ABC</td><td width="63px" style="text-align: right;;">$17,500</td><td width="63px" style="text-align: right;;">3450</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;">$0</td><td width="63px" style="text-align: right;;">$15,000</td><td width="63px" style="text-align: right;;">10%</td><td width="63px" style="text-align: right;;">10%</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">4</td><td width="63px" style=";">Today Company</td><td width="63px" style="text-align: right;;">$30,000</td><td width="63px" style="text-align: right;;">9700</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;">$15,000</td><td width="63px" style="text-align: right;;">$36,000</td><td width="63px" style="text-align: right;;">15%</td><td width="63px" style="text-align: right;;">5%</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">5</td><td width="63px" style=";">ACME</td><td width="63px" style="text-align: right;;">$58,000</td><td width="63px" style="text-align: right;;">24200</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;">$36,000</td><td width="63px" style="text-align: right;;">$45,000</td><td width="63px" style="text-align: right;;">20%</td><td width="63px" style="text-align: right;;">5%</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">6</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;">$45,000</td><td width="63px" style="text-align: right;;">$56,250</td><td width="63px" style="text-align: right;;">25%</td><td width="63px" style="text-align: right;;">5%</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">7</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;">$56,250</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;">25%</td><td width="63px" style="text-align: right;;">0%</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">SUM(<font color="Green">$B$2:$B2</font>)>$F$3:$F$7</font>)+0,(<font color="Red">SUM(<font color="Green">$B$2:$B2</font>)-$F$3:$F$7</font>)+0,$I$3:$I$7</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I3</th><td style="text-align:left">=H3-N(<font color="Blue">H2</font>)</td></tr></tbody></table></td></tr></table><br />
Formula in C2 is copied down for the number of records you hve, and formula in I3 is also copied down. I made an assumption that 25% was the highest rate of commission you pay, hence the entry in H7 of the table.

 

[Firefly2012]

To determine the incremental commission payable, please see the following (formula in D2 copied downwards)

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;background-color: #FFFFFF;;">Name</td><td style="font-weight: bold;border-top: 1px solid black;background-color: #FFFFFF;;">Bookings</td><td style="font-weight: bold;border-top: 1px solid black;background-color: #FFFFFF;;">Total Commission</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;">Incremental Commission</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;border-top: 1px solid black;background-color: #FFFFFF;;">Commission</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="background-color: #FFFFFF;;">Integis</td><td style="text-align: right;background-color: #FFFFFF;;">$10,500</td><td style="text-align: right;background-color: #FFFFFF;;">$1,050</td><td style="text-align: right;background-color: #FFFFFF;;">$1,050</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="font-weight: bold;background-color: #FFFFFF;;">Lower limit</td><td style="font-weight: bold;background-color: #FFFFFF;;">Upper limit</td><td style="font-weight: bold;background-color: #FFFFFF;;">Rate</td><td style="font-weight: bold;border-right: 1px solid black;background-color: #FFFFFF;;">Differential Rate</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #FFFFFF;;">ABC</td><td style="text-align: right;background-color: #FFFFFF;;">$17,500</td><td style="text-align: right;background-color: #FFFFFF;;">$3,450</td><td style="text-align: right;background-color: #FFFFFF;;">$2,400</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;">$0</td><td style="text-align: right;background-color: #FFFFFF;;">$15,000</td><td style="text-align: right;background-color: #FFFFFF;;">10%</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">10%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #FFFFFF;;">Today Company</td><td style="text-align: right;background-color: #FFFFFF;;">$30,000</td><td style="text-align: right;background-color: #FFFFFF;;">$9,700</td><td style="text-align: right;background-color: #FFFFFF;;">$6,250</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;">$15,000</td><td style="text-align: right;background-color: #FFFFFF;;">$36,000</td><td style="text-align: right;background-color: #FFFFFF;;">15%</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">5%</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="background-color: #FFFFFF;;">ACME</td><td style="text-align: right;background-color: #FFFFFF;;">$58,000</td><td style="text-align: right;background-color: #FFFFFF;;">$24,200</td><td style="text-align: right;background-color: #FFFFFF;;">$14,500</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;">$36,000</td><td style="text-align: right;background-color: #FFFFFF;;">$45,000</td><td style="text-align: right;background-color: #FFFFFF;;">20%</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">5%</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;">$45,000</td><td style="text-align: right;background-color: #FFFFFF;;">$56,250</td><td style="text-align: right;background-color: #FFFFFF;;">25%</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">5%</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;">$56,250</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;">25%</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;">0%</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=C2-N(<font color="Blue">C1</font>)</td></tr></tbody></table></td></tr></table><br />

 

[buddyosher]

Thanks, I used that idea, made a couple changes and it worked. Thanks!!!

Hi Jon,
I am putting forward a solution based on my understanding of your problem.
Pls check whether it is yielding the desired result what you want or not.

Excel Workbook
	A	B	C
1	Name	Bookings	Commission
2	Integis	$10,500	1500
3	ABC company	$17,500	1875
4	Today company	$30,000	3750
5	anuary	$58,000	12250
Sheet1

 

[buddyosher]

This looks great, but if you do the math, the last commission is paid out at about 50%. Or am I looking at this wrong?

You want to do it on a cumulative basis, so assuming I am correct, you want this:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td width="25px" style="color: #161120;text-align: center;">1</td><td width="63px" style="font-weight: bold;;">Name</td><td width="63px" style="font-weight: bold;;">Bookings</td><td width="63px" style="font-weight: bold;;">Commission</td><td width="63px" style="font-weight: bold;text-align: right;;"></td><td width="63px" style="font-weight: bold;text-align: right;;"></td><td width="63px" style="font-weight: bold;;">Commission</td><td width="63px" style="font-weight: bold;text-align: right;;"></td><td width="63px" style="font-weight: bold;text-align: right;;"></td><td width="63px" style="font-weight: bold;text-align: right;;"></td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">2</td><td width="63px" style=";">Integis</td><td width="63px" style="text-align: right;;">$10,500</td><td width="63px" style="text-align: right;;">1050</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="font-weight: bold;;">Lower limit</td><td width="63px" style="font-weight: bold;;">Upper limit</td><td width="63px" style="font-weight: bold;;">Rate</td><td width="63px" style="font-weight: bold;;">Differential Rate</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">3</td><td width="63px" style=";">ABC</td><td width="63px" style="text-align: right;;">$17,500</td><td width="63px" style="text-align: right;;">3450</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;">$0</td><td width="63px" style="text-align: right;;">$15,000</td><td width="63px" style="text-align: right;;">10%</td><td width="63px" style="text-align: right;;">10%</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">4</td><td width="63px" style=";">Today Company</td><td width="63px" style="text-align: right;;">$30,000</td><td width="63px" style="text-align: right;;">9700</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;">$15,000</td><td width="63px" style="text-align: right;;">$36,000</td><td width="63px" style="text-align: right;;">15%</td><td width="63px" style="text-align: right;;">5%</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">5</td><td width="63px" style=";">ACME</td><td width="63px" style="text-align: right;;">$58,000</td><td width="63px" style="text-align: right;;">24200</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;">$36,000</td><td width="63px" style="text-align: right;;">$45,000</td><td width="63px" style="text-align: right;;">20%</td><td width="63px" style="text-align: right;;">5%</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">6</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;">$45,000</td><td width="63px" style="text-align: right;;">$56,250</td><td width="63px" style="text-align: right;;">25%</td><td width="63px" style="text-align: right;;">5%</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">7</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;">$56,250</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;">25%</td><td width="63px" style="text-align: right;;">0%</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">SUM(<font color="Green">$B$2:$B2</font>)>$F$3:$F$7</font>)+0,(<font color="Red">SUM(<font color="Green">$B$2:$B2</font>)-$F$3:$F$7</font>)+0,$I$3:$I$7</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I3</th><td style="text-align:left">=H3-N(<font color="Blue">H2</font>)</td></tr></tbody></table></td></tr></table><br />
Formula in C2 is copied down for the number of records you hve, and formula in I3 is also copied down. I made an assumption that 25% was the highest rate of commission you pay, hence the entry in H7 of the table.

 

[Firefly2012]

That was cumulative commission earned on all the sales to date - so total commission earned on the 4 sales in total.

 

[MrVillareal]

try this, take note of the space colored with yellow,
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">10,500.00</td><td style="text-align: right;;">1,050.00</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">17,500.00</td><td style="text-align: right;;">2,400.00</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">30,000.00</td><td style="text-align: right;;">6,250.00</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">58,000.00</td><td style="text-align: right;;">14,500.00</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;background-color: #FFFF00;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">15,000.00</td><td style="text-align: right;;">10%</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">36,000.00</td><td style="text-align: right;;">15%</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">45,000.00</td><td style="text-align: right;;">20%</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">56,250.00</td><td style="text-align: right;;">25%</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">SUM(<font color="Green">$A$1:A2</font>)>=$A$7:$A$10</font>),(<font color="Red">SUM(<font color="Green">$A$1:A2</font>)-$A$7:$A$10</font>),$B$8:$B$11-$B$7:$B$10</font>)-SUM(<font color="Blue">$B$1:B1</font>)</td></tr></tbody></table></td></tr></table><br />