commission

vinod9111

Active Member
Joined
Jan 21, 2009
Messages
425
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

vinod9111

Active Member
Joined
Jan 21, 2009
Messages
425
Thanx Shemayisroel for your answer, but unable to open the link , error throws "domain not exist".
 

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867

ADVERTISEMENT

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

Active Member
Joined
Jan 21, 2009
Messages
425
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

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
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

Well-known Member
Joined
Feb 15, 2002
Messages
4,768
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
try

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

Watch MrExcel Video

Forum statistics

Threads
1,133,156
Messages
5,657,158
Members
418,363
Latest member
Debating_Earth

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
Top