commission

[vinod9111]

Hi all,

need one formula to work on the given below data

<TABLE style="WIDTH: 108pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=144 border=0 x:str><COLGROUP><COL style="WIDTH: 54pt" span=2 width=72><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=72 height=20>Achievement</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=72>Commission</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="21000000">21000000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=1000000*0.1">100000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>21100000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=+B2+1000">101000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="21200000" x:fmla="=+A3+100000">21200000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=+B3+1000">102000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num x:fmla="=+A4+100000">21300000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=+B4+1000">103000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="21400000" x:fmla="=+A5+100000">21400000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=+B5+1000">104000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num x:fmla="=+A6+100000">21500000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=+B6+1000">105000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="21600000" x:fmla="=+A7+100000">21600000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=+B7+1000">106000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num x:fmla="=+A8+100000">21700000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=+B8+1000">107000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="21800000" x:fmla="=+A9+100000">21800000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=+B9+1000">108000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num x:fmla="=+A10+100000">21900000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=+B10+1000">109000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="22000000" x:fmla="=+A11+100000">22000000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=2000000*0.1">200000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num x:fmla="=+A12+100000">22100000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=+B12+1000">201000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num="22200000" x:fmla="=+A13+100000">22200000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=+B13+1000">202000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num x:fmla="=+A14+100000">22300000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=+B14+1000">203000</TD></TR></TBODY></TABLE>

for the amount crossed over 2,10,00,000/-, commission would be paid 10% on 10,00,000/- and plus 1000/- for every increase on 10,00,000/- till it reaches 2,20,00,000/- where it will be paid flat 10% on 20,00,000/- then again 10% plus 1000/- for every increase on 20,00,000/-.Expected results is provided in commission column.

any help will be appreciated.

 

[vinod9111]

any ideas guys ......

 

[shemayisroel]

I don't entirely understand what you want. Not sure if the below will help you but i recently came across it.

https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/

Search for: Commission Structures (Complex vs Simple Tiers).xls

 

[vinod9111]

Thanx Shemayisroel for your answer, but unable to open the link , error throws "domain not exist".

 

[shemayisroel]

Thanx Shemayisroel for your answer, but unable to open the link , error throws "domain not exist".

Hmmm

Not sure why it appeared that way, I'll try again...

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/</TD></TR></TBODY></TABLE>

 

[vinod9111]

the site is restricted, not able to access it from my office. Will try to access from some other location.

thanx any way for your help.

 

[shemayisroel]

the site is restricted, not able to access it from my office. Will try to access from some other location.

thanx any way for your help.

Ouch! No problems. The below is the set up - see if this helps.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BACKGROUND-COLOR: #ccffff"> </TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">Tier Begins</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">Commiss %</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">Differential</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BACKGROUND-COLOR: #c0c0c0">Tier 1 Commissions</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center"> - </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">15%</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">15%</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BACKGROUND-COLOR: #c0c0c0">Tier 2 Commissions</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center"> 80,001.00 </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">20%</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">5%</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BACKGROUND-COLOR: #c0c0c0">Tier 3 Commissions</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center"> 150,001.00 </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">22%</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">2%</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BACKGROUND-COLOR: #c0c0c0">Tier 4 Commissions</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center"> 400,001.00 </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">25%</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">3%</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BACKGROUND-COLOR: #c0c0c0">Tier 5 Commissions</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center"> 750,001.00 </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">30%</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">5%</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="BACKGROUND-COLOR: #ffcc99"> </TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Sales </TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Tiered Disc </TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Simple Disc </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="BACKGROUND-COLOR: #ffff99">Sales Person 1</TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ 33,000.00 </TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ 4,950.00 </TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ 4,950.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="BACKGROUND-COLOR: #ffff99">Sales Person 2</TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ 135,000.00 </TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ 22,999.95 </TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ 27,000.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="BACKGROUND-COLOR: #ffff99">Sales Person 3</TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ 190,000.00 </TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ 34,799.93 </TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ 41,800.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="BACKGROUND-COLOR: #ffff99">Sales Person 4</TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ 425,000.00 </TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ 87,249.90 </TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ 106,250.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="BACKGROUND-COLOR: #ffff99">Sales Person 5</TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ 1,000,000.00 </TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ 243,499.85 </TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ 300,000.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="BACKGROUND-COLOR: #ffff99">Sales Person 6</TD><TD style="BACKGROUND-COLOR: #ffff99"> </TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ - </TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ - </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="BACKGROUND-COLOR: #ffff99">Sales Person 7</TD><TD style="BACKGROUND-COLOR: #ffff99"> </TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ - </TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ - </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="BACKGROUND-COLOR: #ffff99">Sales Person 8</TD><TD style="BACKGROUND-COLOR: #ffff99"> </TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ - </TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: right">$ - </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D4</TD><TD>=C4-N(C3)</TD></TR><TR><TD>D5</TD><TD>=C5-N(C4)</TD></TR><TR><TD>D6</TD><TD>=C6-N(C5)</TD></TR><TR><TD>D7</TD><TD>=C7-N(C6)</TD></TR><TR><TD>D8</TD><TD>=C8-N(C7)</TD></TR><TR><TD>C13</TD><TD>=SUMPRODUCT(--($B13 >$B$4:$B$8), $B13 - $B$4:$B$8, $D$4:$D$8)</TD></TR><TR><TD>D13</TD><TD>=$B13 * LOOKUP($B13,$B$4:$B$8,$C$4:$C$8)</TD></TR><TR><TD>C14</TD><TD>=SUMPRODUCT(--($B14 >$B$4:$B$8), $B14 - $B$4:$B$8, $D$4:$D$8)</TD></TR><TR><TD>D14</TD><TD>=$B14 * LOOKUP($B14,$B$4:$B$8,$C$4:$C$8)</TD></TR><TR><TD>C15</TD><TD>=SUMPRODUCT(--($B15 >$B$4:$B$8), $B15 - $B$4:$B$8, $D$4:$D$8)</TD></TR><TR><TD>D15</TD><TD>=$B15 * LOOKUP($B15,$B$4:$B$8,$C$4:$C$8)</TD></TR><TR><TD>C16</TD><TD>=SUMPRODUCT(--($B16 >$B$4:$B$8), $B16 - $B$4:$B$8, $D$4:$D$8)</TD></TR><TR><TD>D16</TD><TD>=$B16 * LOOKUP($B16,$B$4:$B$8,$C$4:$C$8)</TD></TR><TR><TD>C17</TD><TD>=SUMPRODUCT(--($B17 >$B$4:$B$8), $B17 - $B$4:$B$8, $D$4:$D$8)</TD></TR><TR><TD>D17</TD><TD>=$B17 * LOOKUP($B17,$B$4:$B$8,$C$4:$C$8)</TD></TR><TR><TD>C18</TD><TD>=SUMPRODUCT(--($B18 >$B$4:$B$8), $B18 - $B$4:$B$8, $D$4:$D$8)</TD></TR><TR><TD>D18</TD><TD>=$B18 * LOOKUP($B18,$B$4:$B$8,$C$4:$C$8)</TD></TR><TR><TD>C19</TD><TD>=SUMPRODUCT(--($B19 >$B$4:$B$8), $B19 - $B$4:$B$8, $D$4:$D$8)</TD></TR><TR><TD>D19</TD><TD>=$B19 * LOOKUP($B19,$B$4:$B$8,$C$4:$C$8)</TD></TR><TR><TD>C20</TD><TD>=SUMPRODUCT(--($B20 >$B$4:$B$8), $B20 - $B$4:$B$8, $D$4:$D$8)</TD></TR><TR><TD>D20</TD><TD>=$B20 * LOOKUP($B20,$B$4:$B$8,$C$4:$C$8)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 315pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=419 border=0 x:str><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" span=4 width=85><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=79 height=17>TIERED</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 64pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=85></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 64pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=85></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 64pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=85></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 64pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=85></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=5 height=17>The complex tier pays the commission/percentage based</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=5 height=17 x:str="on each tier range, so the first portion is always paid at the ">on each tier range, so the first portion is always paid at the </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=5 height=17>first tier, the second portion at the second tier, etc. Then the</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=3 height=17 x:str="tiered payment values are added. ">tiered payment values are added. </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=5 height=17>This structure always pays MORE than "Simple" if the differential</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=5 height=17>values are decreasing, always pays LESS than "Simple"</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=3 height=17>if the differential values are increasing.</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>SIMPLE</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=5 height=17>The simple tier takes the grand total and applies the appropriate</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=4 height=17>percentage for that amount to the entire total.</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=5 height=17>This structure always pays MORE than "Complex" if the differential</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=5 height=17 x:str='values are increasing, always pays LESS than "Complex" '>values are increasing, always pays LESS than "Complex" </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=3 height=17>if the differential values are dencreasing.</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

 

[Dave Patton]

try

=(A2>=21000000)*(100000+ROUNDDOWN((A2-21000000)/100,0))+(A2>=22000000)*(90000)