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>
 
Sort by date Sort by votes
My assumption was that you get paid commissions based on cumulative sales for the year, starting at a low commission, and increasing as the cumulative values increase. Because of that, each month you calculate the total commissions for the year, and then subtract the amount already paid to figure how much you get paid this month.

If that's not the case, please explain how your commission plan works, in words.
 
Last edited:
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
We calculate commission based on what is sold per month as you explained. Each month the commission % resets to one level below (n-1) i.e. if you were at 30%, you would start the next month at 25%.

This starts over every quarter, with the first month starting at 15% and paying everything at 15%, then at 20% paying that amount (which turns out to be $9,000), then 25% (which is about about $9,000 too) and so on. Each additional month in the quarter does NOT pay progressively, so it is much easier and then just pays at the new amount.

In that case MONTH 1, $58,000 is sold it would look like this:
up to 36k @ 15% = $5,400
up to 45k @ 20% ... the next $9,000 = $1,800
up to 56k @ 25% ... the next $9,000 = $2,250
up to 72k @ 30% ... the next $2,000 = $ 600
for a total of $10,050

Since they ended the month at 30%, the next month (n-1) would start at 25% and would look like this:

In that case MONTH 2, $47,000 is sold it would look like this: (35% is max)
up to 36k @ 25% = $9,000
up to 45k @ 30% ... the next $9,000 = $2,700
up to 56k @ 35% ... the next $9,000 = $3,150
up to 72k @ 35% ... the next $2,000 = $ 700
for a total of $15,550

Hope that makes sense... thanks again for all of your help!
 
Upvote 0
Maybe this?

<TABLE style="WIDTH: 597pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=797><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 3657" width=80><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2706" width=59><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2816" width=62><COL style="WIDTH: 37pt" width=49><COL style="WIDTH: 410pt; mso-width-source: userset; mso-width-alt: 25014" width=547><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 60pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 height=16 width=80> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 44pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=59>Month1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 46pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=62>Month2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f3f3f3; WIDTH: 37pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=49>Month3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; WIDTH: 410pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=547> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 height=16> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl35 height=16 align=right>0 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33 align=right>15%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 align=right>25%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 align=right>20%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39>C3 and across and down: =IFERROR(INDEX($B$3:$B$7, B$11+ ROWS(C$3:C3) - 2), B$7)</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl35 height=16 align=right>36,000 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33 align=right>20%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 align=right>30%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 align=right>25%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl35 height=16 align=right>45,000 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33 align=right>25%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 align=right>35%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 align=right>30%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl35 height=16 align=right>56,000 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33 align=right>30%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 align=right>35%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 align=right>35%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39> </TD></TR><TR style="HEIGHT: 11.4pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 11.4pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl35 height=15 align=right>72,000 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl33 align=right>35%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 align=right>35%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl38 align=right>35%</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31 height=16> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl31> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 60pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl37 height=16 width=80>Sales</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl35 align=right>58,000 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl35 align=right>47,000 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl35 align=right>30,000 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 60pt; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl37 height=16 width=80>Commission</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl34> 10,550 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl34> 16,450 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl34> 6,000 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39>B10 and across: =SUMPRODUCT( (B9 > $A$3:$A$7) * (B9 - A$3:A$7) * (B$3:B$7 - B$2:B$6) )</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 60pt; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl37 height=16 width=80>Ending Level</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 align=right>4 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 align=right>3 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl36 align=right>1 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl39>B11 and across: =MATCH(B9, $A$3:$A$7)</TD></TR></TBODY></TABLE>


In Month 1, commissions are 10,550 (not 10,050). In month2, you start on Level 3, in Month 3, on level 2.
 
Upvote 0
This is great SHG for the monthly total, but how would I use this for each pay item as seen in in
excel2.gif


So Opportunity Name 1 would be paid at 15% and Opportunity Name 2 would pay $20,300 @ 15% and $1,200 @ 20%. Can that be done in one formula with the SUMPRODUCT ?
 
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
879
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
647
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,216,562
Messages
6,131,422
Members
449,651
Latest member
Jacobs22

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