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.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

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
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,160
Office Version
2010
Platform
Windows
try

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,997
Messages
5,508,670
Members
408,689
Latest member
SamSan78

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top