Progressive Commission Calculations

B

buddyosher

New Member
Joined
Jan 15, 2012
Messages
31
<html>

<head>


<p class=MsoNormal>I am looking to create a formula to calculate progressive
commission payouts. Can I create a payout that separates a full amount ($12,000
sale) in to 3 levels of payouts? See the example below, if I sell $12,000, and
the first $5,000 is paid at 5%, from $5,001-$10,000 I am paid 10% and from
$10,000-$12,000 I am paid 15%. The long hand calculations are below, but
creating a formula that would calculate would be great.* Can this be done?</p>

<table class=MsoNormalTable border=1 cellspacing=0 cellpadding=0 width=521
style='width:390.45pt;margin-left:4.65pt;border-collapse:collapse;border:none'>
<tr style='height:15.0pt'>
<td width=277 colspan=3 rowspan=2 valign=bottom style='width:207.75pt;
border:solid windowtext 1.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><span style='color:black'>Sale of $12,000 = progressive payout* </span></p>
</td>
<td width=22 nowrap valign=bottom style='width:16.75pt;border:solid windowtext 1.0pt;
border-left:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'></td>
<td width=116 nowrap valign=bottom style='width:86.75pt;border:solid windowtext 1.0pt;
border-left:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><span style='color:black'>Pay</span></p>
</td>
<td width=84 nowrap valign=bottom style='width:63.0pt;border:solid windowtext 1.0pt;
border-left:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><span style='color:black'>Rate</span></p>
</td>
<td width=22 nowrap valign=bottom style='width:16.2pt;border:solid windowtext 1.0pt;
border-left:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'></td>
</tr>
<tr style='height:15.0pt'>
<td width=22 nowrap valign=bottom style='width:16.75pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'></td>
<td width=116 nowrap valign=bottom style='width:86.75pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><span style='color:black'>*$******* 5,000 </span></p>
</td>
<td width=84 nowrap valign=bottom style='width:63.0pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal align=right style='margin-bottom:0in;margin-bottom:.0001pt;
text-align:right;line-height:normal'><span style='color:black'>5%</span></p>
</td>
<td width=22 nowrap valign=bottom style='width:16.2pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'></td>
</tr>
<tr style='height:15.0pt'>
<td width=79 nowrap valign=bottom style='width:59.25pt;border:solid windowtext 1.0pt;
border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><b><span style='color:black'>12k SOLD</span></b></p>
</td>
<td width=87 nowrap valign=bottom style='width:65.3pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><span style='color:black'>Commission</span></p>
</td>
<td width=111 nowrap valign=bottom style='width:83.2pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><span style='color:black'>Payout</span></p>
</td>
<td width=22 nowrap valign=bottom style='width:16.75pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'></td>
<td width=116 nowrap valign=bottom style='width:86.75pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><span style='color:black'>*$**** 10,000 </span></p>
</td>
<td width=84 nowrap valign=bottom style='width:63.0pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal align=right style='margin-bottom:0in;margin-bottom:.0001pt;
text-align:right;line-height:normal'><span style='color:black'>10%</span></p>
</td>
<td width=22 nowrap valign=bottom style='width:16.2pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'></td>
</tr>
<tr style='height:15.0pt'>
<td width=79 nowrap valign=bottom style='width:59.25pt;border:solid windowtext 1.0pt;
border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><span style='color:black'>*$*** 5,000 </span></p>
</td>
<td width=87 nowrap valign=bottom style='width:65.3pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal align=right style='margin-bottom:0in;margin-bottom:.0001pt;
text-align:right;line-height:normal'><span style='color:black'>5%</span></p>
</td>
<td width=111 nowrap valign=bottom style='width:83.2pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><span style='color:black'>*$******* 250 </span></p>
</td>
<td width=22 nowrap valign=bottom style='width:16.75pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'></td>
<td width=116 nowrap valign=bottom style='width:86.75pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><span style='color:black'>*$**** 15,000 </span></p>
</td>
<td width=84 nowrap valign=bottom style='width:63.0pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal align=right style='margin-bottom:0in;margin-bottom:.0001pt;
text-align:right;line-height:normal'><span style='color:black'>15%</span></p>
</td>
<td width=22 nowrap valign=bottom style='width:16.2pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'></td>
</tr>
<tr style='height:15.0pt'>
<td width=79 nowrap valign=bottom style='width:59.25pt;border:solid windowtext 1.0pt;
border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><span style='color:black'>*$*** 5,000 </span></p>
</td>
<td width=87 nowrap valign=bottom style='width:65.3pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal align=right style='margin-bottom:0in;margin-bottom:.0001pt;
text-align:right;line-height:normal'><span style='color:black'>10%</span></p>
</td>
<td width=111 nowrap valign=bottom style='width:83.2pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><span style='color:black'>*$******* 500 </span></p>
</td>
<td width=22 nowrap valign=bottom style='width:16.75pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'></td>
<td width=116 nowrap valign=bottom style='width:86.75pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'></td>
<td width=84 nowrap valign=bottom style='width:63.0pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'></td>
<td width=22 nowrap valign=bottom style='width:16.2pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.0pt'></td>
</tr>
<tr style='height:15.75pt'>
<td width=79 nowrap valign=bottom style='width:59.25pt;border:solid windowtext 1.0pt;
border-top:none;padding:0in 5.4pt 0in 5.4pt;height:15.75pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><span style='color:black'>*$*** 2,000 </span></p>
</td>
<td width=87 nowrap valign=bottom style='width:65.3pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.75pt'>
<p class=MsoNormal align=right style='margin-bottom:0in;margin-bottom:.0001pt;
text-align:right;line-height:normal'><span style='color:black'>15%</span></p>
</td>
<td width=111 nowrap valign=bottom style='width:83.2pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.75pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><span style='color:black'>*$******* 300 </span></p>
</td>
<td width=22 nowrap valign=bottom style='width:16.75pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.75pt'></td>
<td width=116 nowrap valign=bottom style='width:86.75pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.75pt'></td>
<td width=84 nowrap valign=bottom style='width:63.0pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.75pt'></td>
<td width=22 nowrap valign=bottom style='width:16.2pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.75pt'></td>
</tr>
<tr style='height:30.0pt'>
<td width=79 style='width:59.25pt;border:solid windowtext 1.0pt;border-top:
none;background:#F2F2F2;padding:0in 5.4pt 0in 5.4pt;height:30.0pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><b><span style='color:black'>*Total Sales </span></b></p>
</td>
<td width=87 valign=bottom style='width:65.3pt;border-top:none;border-left:
none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
background:#F2F2F2;padding:0in 5.4pt 0in 5.4pt;height:30.0pt'>
<p class=MsoNormal align=center style='margin-bottom:0in;margin-bottom:.0001pt;
text-align:center;line-height:normal'><b><span style='color:black'>weighted
AVG %</span></b></p>
</td>
<td width=111 style='width:83.2pt;border-top:none;border-left:none;
border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
background:#F2F2F2;padding:0in 5.4pt 0in 5.4pt;height:30.0pt'>
<p class=MsoNormal align=center style='margin-bottom:0in;margin-bottom:.0001pt;
text-align:center;line-height:normal'><b><span style='color:black'>*Total
Commiss </span></b></p>
</td>
<td width=22 nowrap valign=bottom style='width:16.75pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:30.0pt'></td>
<td width=116 nowrap valign=bottom style='width:86.75pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:30.0pt'></td>
<td width=84 nowrap valign=bottom style='width:63.0pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:30.0pt'></td>
<td width=22 nowrap valign=bottom style='width:16.2pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:30.0pt'></td>
</tr>
<tr style='height:15.75pt'>
<td width=79 valign=bottom style='width:59.25pt;border:solid windowtext 1.0pt;
border-top:none;background:#F2F2F2;padding:0in 5.4pt 0in 5.4pt;height:15.75pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><b><span style='color:black'>*$* 12,000 </span></b></p>
</td>
<td width=87 valign=bottom style='width:65.3pt;border-top:none;border-left:
none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
background:#F2F2F2;padding:0in 5.4pt 0in 5.4pt;height:15.75pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><b><span style='color:black'> </span></b></p>
</td>
<td width=111 valign=bottom style='width:83.2pt;border-top:none;border-left:
none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
background:#F2F2F2;padding:0in 5.4pt 0in 5.4pt;height:15.75pt'>
<p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
normal'><b><span style='color:black'>*$*** 1,050 </span></b></p>
</td>
<td width=22 nowrap valign=bottom style='width:16.75pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.75pt'></td>
<td width=116 nowrap valign=bottom style='width:86.75pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.75pt'></td>
<td width=84 nowrap valign=bottom style='width:63.0pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.75pt'></td>
<td width=22 nowrap valign=bottom style='width:16.2pt;border-top:none;
border-left:none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
padding:0in 5.4pt 0in 5.4pt;height:15.75pt'></td>
</tr>
</table>

<p class=MsoNormal> </p>

</div>

</body>

</html>
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
If the sales amount is in cell A2, this formula will calculate total commission:
=IF(A2<=5000,A2*0.05,IF(A2<=10000,5000*0.05+(A2-5000)*0.1,5000*0.05+(10000-5000)*0.1+(A2-10000)*0.15))

Note you could change the 5000 and 10000's & the rates in the formula to the appropriate cells in your rate schedule which would then reflect any change you make to the rate schedule.
 
Upvote 0
Thank you both for your reply.

WEST MAN, will you please explain more about the MAX formula. It is always *.05. How would the formula work if instead of (5%, 10%, 15%) it was (6%,9%, 13%)?
 
Upvote 0
There are 3 parts to the formula seperated by the plus signs.

The first part computes 5% commission on the entire amount. The second part adds an additional 5% on anything above $5000 (a total of 10%). Similarly the third part adds an additional 5% (15% total) on anything above $10,000.

The max is required to prevent negative comission on smaller sales. Notice that in the second part if sales would be $4000, A2-5000 would be negative. A max of zero prevents this.

For 6%, 9% and 13%, you would use .06, .03 and .04. The initial commission % and the incremental commissions.
 
Upvote 0
Thanks again West Man. To dig a little deeper, <p><img border="0" src="http://www.jonknows.com/excel.gif" width="934" height="294"></p>

<p>Here is the formula I am using to compute how to pay on each deal. My problem
is I cannot figure out how to pay progressively for all deals, so Company #3
would mean the the pay rate would start at $37,020 (D3+D4 above) which is 20%
(36k through 45k = 20%) and upward past the max payout % at $72,000 & 35%.</p>
<p>I am trying to have everything be part of the formula, so for example using
Company 3 above, for each payment level instead of adding
<span style="color: rgb(0, 0, 0); font-family: verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: rgb(239, 239, 239); display: inline !important; float: none; ">
(MAX(0,C6-9000)*0.05)</span> I changed it to be:</p>
<p><br>
M6-M5= $9,000<br>
&<br>
N6-N5=5%</p>
<p>Below is my full formula, but it does not combine the previous payments. Any
thoughts on how to combine</p>
<p>
=IF($D$2<=$M$4,D3*$N$4,(D3*$N$4+(MAX(0,(D3-($M$6-$M$5))*($N$6-$N$5)))+(MAX(0,(D3-($M$7-$M$6))*($N$7-$N$6)))+(MAX(0,(D3-($M$8-$M$7))*($N$8-$N$7)))+(MAX(0,(D3-($M$8-$M$9))*($N$8-$N$9)))))</p>
 
Upvote 0
Another way, maybe.

<TABLE style="WIDTH: 283pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=377><COLGROUP><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2596" width=57><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2998" width=66><COL style="WIDTH: 37pt" width=49><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 3108" span=2 width=68><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 3145" width=69><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 43pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 height=16 width=57>Amount</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 49pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 width=66>Rate</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #909090; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=49> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 width=68>Sale</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 width=68>Cumulative</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 52pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 width=69>Commission</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl35 height=16> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #909090; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 align=right>15700 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 align=right>15700 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 align=right>1605 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 height=16 align=right>0 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34 align=right>5%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #909090; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 align=right>21500 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 align=right>37200 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 align=right>3285 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 height=16 align=right>5000 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34 align=right>10%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #909090; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 align=right>40000 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 align=right>77200 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 align=right>8260 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 height=16 align=right>10000 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34 align=right>15%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #909090; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 align=right>21500 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 align=right>98700 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 align=right>5375 </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 height=16 align=right>36000 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34 align=right>20%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #909090; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36 height=16 align=right>72000 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34 align=right>25%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #909090; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl36> </TD></TR></TBODY></TABLE>

Columns A and B containg the rate table, with a blank above the first amount. So you get paid 5% on the first 5000, 10% from 5000 to 10000, etc.

The formula in F2 and down is

=SUMPRODUCT((E2>$A$3:$A$7)*(E2-$A$3:$A$7)*($B$3:$B$7 - $B$2:$B$6)) - SUM(F$1:F1)
 
Upvote 0
SHG

Maybe I am understanding this wrong, but I cannot come up with the same numbers... or maybe I really don't understand the function. Here is what I found:


<table border="0" cellspacing="0" cellpadding="0" style="color: rgb(0, 0, 0); font-family: verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; width: 456px; border-collapse: collapse; background-color: rgb(239, 239, 239)">
<tr height="16" style="height: 12pt; ">
<td class="xl31" height="16" style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 10pt; font-family: verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; width: 87px; height: 12pt; border: 0.5pt solid windowtext; background-color: rgb(243, 243, 243)">
<font size="2" face="Tahoma">Amount</font></td>
<td class="xl31" style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 10pt; font-family: verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; width: 53px; border-left: initial initial windowtext; border-right: 0.5pt solid windowtext; border-top: 0.5pt solid windowtext; border-bottom: 0.5pt solid windowtext; background-color: rgb(243, 243, 243)">
<font size="2" face="Tahoma">Rate</font></td>
<td class="xl32" style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 10pt; font-family: verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; width: 18px; border-left: initial initial windowtext; border-right: 0.5pt solid windowtext; border-top: 0.5pt solid windowtext; border-bottom: 0.5pt solid windowtext; background-color: rgb(144, 144, 144)"> </td>
<td class="xl31" style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 10pt; font-family: verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; width: 86px; border-left: initial initial windowtext; border-right: 0.5pt solid windowtext; border-top: 0.5pt solid windowtext; border-bottom: 0.5pt solid windowtext; background-color: rgb(243, 243, 243)">
<font size="2" face="Tahoma">Sale</font></td>
<td class="xl31" style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 10pt; font-family: verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; width: 87px; border-left: initial initial windowtext; border-right: 0.5pt solid windowtext; border-top: 0.5pt solid windowtext; border-bottom: 0.5pt solid windowtext; background-color: rgb(243, 243, 243)">
<font size="2" face="Tahoma">Cumulative</font></td>
<td class="xl31" style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 10pt; font-family: verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; width: 106px; border-left: initial initial windowtext; border-right: 0.5pt solid windowtext; border-top: 0.5pt solid windowtext; border-bottom: 0.5pt solid windowtext; background-color: rgb(243, 243, 243)">
<font size="2" face="Tahoma">Commission</font></td>
</tr>
<tr height="16" style="height: 12pt; ">
<td class="xl35" height="16" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: 0.5pt; border-left-style: solid; background-color: white; height: 12pt; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
</td>
<td class="xl33" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; "> </td>
<td class="xl32" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: rgb(144, 144, 144); border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; "> </td>
<td class="xl36" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">15700</font></td>
<td class="xl36" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">15700</font></td>
<td class="xl36" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">1605</font></td>
</tr>
<tr height="16" style="height: 12pt; ">
<td class="xl36" height="16" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: 0.5pt; border-left-style: solid; background-color: white; height: 12pt; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">0</font></td>
<td class="xl34" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">5%</font></td>
<td class="xl32" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: rgb(144, 144, 144); border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; "> </td>
<td class="xl36" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">21500</font></td>
<td class="xl36" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">37200</font></td>
<td class="xl36" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">3285</font></td>
</tr>
<tr height="16" style="height: 12pt; ">
<td class="xl36" height="16" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: 0.5pt; border-left-style: solid; background-color: white; height: 12pt; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">5000</font></td>
<td class="xl34" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">10%</font></td>
<td class="xl32" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: rgb(144, 144, 144); border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; "> </td>
<td class="xl36" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">40000</font></td>
<td class="xl36" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">77200</font></td>
<td class="xl36" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">8260</font></td>
</tr>
<tr height="16" style="height: 12pt; ">
<td class="xl36" height="16" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: 0.5pt; border-left-style: solid; background-color: white; height: 12pt; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">10000</font></td>
<td class="xl34" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">15%</font></td>
<td class="xl32" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: rgb(144, 144, 144); border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; "> </td>
<td class="xl36" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">21500</font></td>
<td class="xl36" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">98700</font></td>
<td class="xl36" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">5375</font></td>
</tr>
<tr height="16" style="height: 12pt; ">
<td class="xl36" height="16" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: 0.5pt; border-left-style: solid; background-color: white; height: 12pt; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">36000</font></td>
<td class="xl34" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">20%</font></td>
<td class="xl32" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: rgb(144, 144, 144); border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; "> </td>
<td class="xl36" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; "> </td>
<td class="xl36" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; "> </td>
<td class="xl36" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; "> </td>
</tr>
<tr height="16" style="height: 12pt; ">
<td class="xl36" height="16" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: 0.5pt; border-left-style: solid; background-color: white; height: 12pt; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">72000</font></td>
<td class="xl34" align="right" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; ">
<font size="2" face="Calibri">25%</font></td>
<td class="xl32" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: rgb(144, 144, 144); border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; "> </td>
<td class="xl36" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; "> </td>
<td class="xl36" style="font: normal normal normal 10pt/normal verdana, geneva, lucida, 'lucida grande', arial, helvetica, sans-serif; border-bottom-color: windowtext; border-bottom-width: 0.5pt; border-bottom-style: solid; border-left-color: windowtext; border-left-width: initial; border-left-style: initial; background-color: white; border-top-color: windowtext; border-top-width: initial; border-top-style: initial; border-right-color: windowtext; border-right-width: 0.5pt; border-right-style: solid; "> </td>
</tr>
</table>
<br class="Apple-interchange-newline">
 <table border="0" cellpadding="0" cellspacing="0" width="171" style="border-collapse:
collapse;width:129pt">
<colgroup>
<col width="97" style="width: 73pt"><col width="74" style="width: 56pt">
</colgroup>
<tr height="20" style="height:15.0pt">
<td height="20" width="97" style="height: 15.0pt; width: 73pt; color: black; font-size: 11.0pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; text-align: general; vertical-align: bottom; white-space: nowrap; border-left: initial initial; border-right: medium none; border-top: initial initial; border-bottom: medium none; padding-left: 1px; padding-right: 1px; padding-top: 1px">
 =5000*0.05</td>
<td width="74" style="width: 56pt; color: black; font-size: 11.0pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; text-align: general; vertical-align: bottom; white-space: nowrap; border: medium none; padding-left: 1px; padding-right: 1px; padding-top: 1px">
250 </td>
</tr>
<tr height="20" style="height:15.0pt">
<td height="20" style="height: 15.0pt; color: black; font-size: 11.0pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; text-align: general; vertical-align: bottom; white-space: nowrap; border-left: initial initial; border-right: medium none; border-top: initial initial; border-bottom: medium none; padding-left: 1px; padding-right: 1px; padding-top: 1px">
 =5000*0.1</td>
<td style="color: black; font-size: 11.0pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; text-align: general; vertical-align: bottom; white-space: nowrap; border: medium none; padding-left: 1px; padding-right: 1px; padding-top: 1px">
 $           500 </td>
</tr>
<tr height="20" style="height:15.0pt">
<td height="20" style="height: 15.0pt; color: black; font-size: 11.0pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; text-align: general; vertical-align: bottom; white-space: nowrap; border-left: initial initial; border-right: medium none; border-top: initial initial; border-bottom: medium none; padding-left: 1px; padding-right: 1px; padding-top: 1px">
 =5700*0.2</td>
<td style="color: black; font-size: 11.0pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; text-align: general; vertical-align: bottom; white-space: nowrap; border: medium none; padding-left: 1px; padding-right: 1px; padding-top: 1px">
 $        1,140 </td>
</tr>
<tr height="20" style="height:15.0pt">
<td height="20" width="97" style="height: 15.0pt; width: 73pt; color: black; font-size: 10.0pt; text-align: right; vertical-align: middle; white-space: normal; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; border-left: initial initial; border-right: medium none; border-top: initial initial; border-bottom: medium none; padding-left: 1px; padding-right: 1px; padding-top: 1px; background: white">
 $             15,700 </td>
<td style="color: black; font-size: 11.0pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; text-align: general; vertical-align: bottom; white-space: nowrap; border: medium none; padding-left: 1px; padding-right: 1px; padding-top: 1px">
 $        1,890 </td>
</tr>
<tr height="20" style="height:15.0pt">
<td height="20" style="height: 15.0pt; color: black; font-size: 11.0pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; text-align: general; vertical-align: bottom; white-space: nowrap; border-left: initial initial; border-right: medium none; border-top: initial initial; border-bottom: medium none; padding-left: 1px; padding-right: 1px; padding-top: 1px"> </td>
<td style="color: black; font-size: 11.0pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; text-align: general; vertical-align: bottom; white-space: nowrap; border: medium none; padding-left: 1px; padding-right: 1px; padding-top: 1px">
NOT 1605?</td>
</tr>
</table>
 
Upvote 0
Change the rates and breakpoints in columns A and B as appropriate. For my example, for the first 15700,

5% * 5000 = 250
10% * 5000 = 500
15% * 5700 = 855

Total = 1605
 
Last edited:
Upvote 0
SHG, I think I may be doing something wrong still, or I am not understanding SUMPRODUCT correctly <BR>
<img border="0" src="http://www.jonknows.com/excel2.gif">
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
854
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
607
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
M
  • Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M

Forum statistics

Threads
1,215,541
Messages
6,125,413
Members
449,223
Latest member
Narrian

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top