Commission calculation

A

AaronC

New Member
Joined
Jul 5, 2011
Messages
4
Brain frozen.. Trying to get my formula to work for the whole evening. FAILED..

Can someone help?

Commission payout only if sales hits $8000.
Commission for $8000-$12999 range is 1%
commission for $13000-17999 range is 1.5%
Commission for $18000-$22999 range is 2%
Commission for $23000-$27999 range is 2.5%
Commission for $28000-$32999 range is 3%
Commission for $33000-$37999 range is 4%
Commission for $38000 and above is 5.5%

Thanks in advance!!

Brozen Frain..
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
G13 would be the cell with the commision

=IF(G13>=8000,
IF(AND(G13>=8000,G13<=12999),1%,
IF(AND(G13>=13000,G13<=17999),1.5%,
IF(AND(G13>=18000,G13<=22999),2%,
IF(AND(G13>=23000,G13<=27999),2.5%,
IF(AND(G13>=28000,G13<=32999),3%,
IF(AND(G13>=33000,G13<=37999),4%,
IF(G13>=38000,5.5%,"error"))))))))
 
Last edited:
Upvote 0
el_ja said:
G13 would be the cell with the commision

=IF(G13>=8000,
IF(AND(G13>=8000,G13<=12999),1%,
IF(AND(G13>=13000,G13<=17999),1.5%,
IF(AND(G13>=18000,G13<=22999),2%,
IF(AND(G13>=23000,G13<=27999),2.5%,
IF(AND(G13>=28000,G13<=32999),3%,
IF(AND(G13>=33000,G13<=37999),4%,
IF(G13>=38000,5.5%,"error"))))))))
Click to expand...

Thanks for your prompt reply,

I'll need the sum of the total commission, eg. G13 is the cell with final sales figure.
if the sales figure is 8001, then the commission value, say in H13 should be $80.01 based on 1% of sales.
If sales figure is 18000, then total commission should be $129.99(12999*1%)+$75(5000*1.5% which we get from the 13000-18000 range) = $204.99

Thanks again!
 
Upvote 0
Well I recently had to do something like that and I needed a table with the data to calculate the commission, because the ranges could vary and I wouldn't have to edit the whole table. Maybe there is a simpler way to do this but I don't know it :)
Hopefully this works, you should test it thoroughly since it's about money ^^

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: right;;">Sales</td><td style="text-align: right;;">23000</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">Low</td><td style="font-weight: bold;text-align: right;;">High</td><td style="font-weight: bold;text-align: right;;">Range</td><td style="font-weight: bold;;">Percentage</td><td style="font-weight: bold;;">Subtotal</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: right;;">Comission</td><td style="text-align: right;;">304.98</td><td style="text-align: right;;">
</td><td style="text-align: right;;">8000</td><td style="text-align: right;;">12999</td><td style="text-align: right;;">4999</td><td style="text-align: right;;">1.00%</td><td style="text-align: right;;">129.99</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">13000</td><td style="text-align: right;;">17999</td><td style="text-align: right;;">4999</td><td style="text-align: right;;">1.50%</td><td style="text-align: right;;">74.99</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">18000</td><td style="text-align: right;;">22999</td><td style="text-align: right;;">4999</td><td style="text-align: right;;">2.00%</td><td style="text-align: right;;">99.98</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">23000</td><td style="text-align: right;;">27999</td><td style="text-align: right;;">4999</td><td style="text-align: right;;">2.50%</td><td style="text-align: right;;">124.98</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">28000</td><td style="text-align: right;;">32999</td><td style="text-align: right;;">4999</td><td style="text-align: right;;">3.00%</td><td style="text-align: right;;">149.97</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">33000</td><td style="text-align: right;;">37999</td><td style="text-align: right;;">4999</td><td style="text-align: right;;">4.00%</td><td style="text-align: right;;">199.96</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">38000</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">5.50%</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">
</td><td style="text-align: right;;">779.86</td></tr></tbody></table>
Hoja1​

The formula in B2 would be:
=
IF(B1>=D8,(SUM($H$2:$H$7)+((B1-E7)*G8)),
IF(AND(B1>=D7,B1<=E7),(SUM($H$2:$H$6)+((B1-E6)*G7)),
IF(AND(B1>=D6,B1<=E6),(SUM($H$2:$H$5)+((B1-E5)*G6)),
IF(AND(B1>=D5,B1<=E5),(SUM($H$2:$H$4)+((B1-E4)*G5)),
IF(AND(B1>=D4,B1<=E4),(SUM($H$2:$H$3)+((B1-E3)*G4)),
IF(AND(B1>=D3,B1<=E3),(SUM($H$2:$H$2)+((B1-E2)*G3)),
IF(AND(B1>=D2,B1<=E2),B1*G2,
"error")))))))

Low and High would be values, Range would have a substraction formula i.e F2 would have =E2-D2.
Percentage would have values
And subtotal would be only in H2 =E2*G2... in the ones below it would be =F3*G3 and so on
 
Last edited:
Upvote 0
el_ja said:
Well I recently had to do something like that and I needed a table with the data to calculate the commission, because the ranges could vary and I wouldn't have to edit the whole table. Maybe there is a simpler way to do this but I don't know it :)
Hopefully this works, you should test it thoroughly since it's about money ^^

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: right;;">Sales</td><td style="text-align: right;;">23000</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">Low</td><td style="font-weight: bold;text-align: right;;">High</td><td style="font-weight: bold;text-align: right;;">Range</td><td style="font-weight: bold;;">Percentage</td><td style="font-weight: bold;;">Subtotal</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: right;;">Comission</td><td style="text-align: right;;">304.98</td><td style="text-align: right;;">
</td><td style="text-align: right;;">8000</td><td style="text-align: right;;">12999</td><td style="text-align: right;;">4999</td><td style="text-align: right;;">1.00%</td><td style="text-align: right;;">129.99</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">13000</td><td style="text-align: right;;">17999</td><td style="text-align: right;;">4999</td><td style="text-align: right;;">1.50%</td><td style="text-align: right;;">74.99</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">18000</td><td style="text-align: right;;">22999</td><td style="text-align: right;;">4999</td><td style="text-align: right;;">2.00%</td><td style="text-align: right;;">99.98</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">23000</td><td style="text-align: right;;">27999</td><td style="text-align: right;;">4999</td><td style="text-align: right;;">2.50%</td><td style="text-align: right;;">124.98</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">28000</td><td style="text-align: right;;">32999</td><td style="text-align: right;;">4999</td><td style="text-align: right;;">3.00%</td><td style="text-align: right;;">149.97</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">33000</td><td style="text-align: right;;">37999</td><td style="text-align: right;;">4999</td><td style="text-align: right;;">4.00%</td><td style="text-align: right;;">199.96</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">38000</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">5.50%</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">
</td><td style="text-align: right;;">779.86</td></tr></tbody></table>
Hoja1​

The formula in B2 would be:
=
IF(B1>=D8,(SUM($H$2:$H$7)+((B1-E7)*G8)),
IF(AND(B1>=D7,B1<=E7),(SUM($H$2:$H$6)+((B1-E6)*G7)),
IF(AND(B1>=D6,B1<=E6),(SUM($H$2:$H$5)+((B1-E5)*G6)),
IF(AND(B1>=D5,B1<=E5),(SUM($H$2:$H$4)+((B1-E4)*G5)),
IF(AND(B1>=D4,B1<=E4),(SUM($H$2:$H$3)+((B1-E3)*G4)),
IF(AND(B1>=D3,B1<=E3),(SUM($H$2:$H$2)+((B1-E2)*G3)),
IF(AND(B1>=D2,B1<=E2),B1*G2,
"error")))))))

Low and High would be values, Range would have a substraction formula i.e F2 would have =E2-D2.
Percentage would have values
And subtotal would be only in H2 =E2*G2... in the ones below it would be =F3*G3 and so on
Click to expand...

GREAT!! Thanks a million!! :biggrin:
 
Upvote 0
Or try this:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">Sales</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">Commission</TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Value</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">%</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Deduction</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">8.000 </TD><TD style="TEXT-ALIGN: right">80,00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">- </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0,0%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">- </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">8.001 </TD><TD style="TEXT-ALIGN: right">80,01 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">8.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1,0%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">- </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">12.999 </TD><TD style="TEXT-ALIGN: right">129,99 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">13.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1,5%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">65,00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">17.999 </TD><TD style="TEXT-ALIGN: right">204,99 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">18.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2,0%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">155,00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">18.000 </TD><TD style="TEXT-ALIGN: right">205,00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">23.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2,5%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">270,00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">23.000 </TD><TD style="TEXT-ALIGN: right">305,00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">28.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">3,0%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">410,00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">27.999 </TD><TD style="TEXT-ALIGN: right">429,98 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">33.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4,0%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">740,00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">28.000 </TD><TD style="TEXT-ALIGN: right">430,00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">38.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5,5%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1.310,00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">32.999 </TD><TD style="TEXT-ALIGN: right">579,97 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right">37.999 </TD><TD style="TEXT-ALIGN: right">779,96 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right">38.000 </TD><TD style="TEXT-ALIGN: right">780,00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right">50.000 </TD><TD style="TEXT-ALIGN: right">1.440,00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F2</TH><TD style="TEXT-ALIGN: left">=IF(D2<=8000,0,(D2-0.01)*(E2-E1)+F1)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B2</TH><TD style="TEXT-ALIGN: left">=A2*VLOOKUP(A2,$D$2:$F$9,2)-VLOOKUP(A2,$D$2:$F$9,3)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz
 
Upvote 0
markmzz said:
Or try this:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">Sales</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">Commission</TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Value</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">%</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Deduction</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">8.000 </TD><TD style="TEXT-ALIGN: right">80,00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">- </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0,0%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">- </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">8.001 </TD><TD style="TEXT-ALIGN: right">80,01 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">8.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1,0%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">- </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">12.999 </TD><TD style="TEXT-ALIGN: right">129,99 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">13.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1,5%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">65,00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">17.999 </TD><TD style="TEXT-ALIGN: right">204,99 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">18.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2,0%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">155,00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">18.000 </TD><TD style="TEXT-ALIGN: right">205,00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">23.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2,5%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">270,00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">23.000 </TD><TD style="TEXT-ALIGN: right">305,00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">28.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">3,0%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">410,00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">27.999 </TD><TD style="TEXT-ALIGN: right">429,98 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">33.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4,0%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">740,00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">28.000 </TD><TD style="TEXT-ALIGN: right">430,00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">38.000 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5,5%</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1.310,00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">32.999 </TD><TD style="TEXT-ALIGN: right">579,97 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right">37.999 </TD><TD style="TEXT-ALIGN: right">779,96 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right">38.000 </TD><TD style="TEXT-ALIGN: right">780,00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right">50.000 </TD><TD style="TEXT-ALIGN: right">1.440,00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD><TD>*********</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F2</TH><TD style="TEXT-ALIGN: left">=IF(D2<=8000,0,(D2-0.01)*(E2-E1)+F1)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B2</TH><TD style="TEXT-ALIGN: left">=A2*VLOOKUP(A2,$D$2:$F$9,2)-VLOOKUP(A2,$D$2:$F$9,3)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz
Click to expand...

Thanks Mark! :D
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
trying to get data out of a table based on multiple variables
Replies
5
Views
590
oldbrewer
O

Forum statistics

Threads
1,224,522
Messages
6,179,293
Members
452,902
Latest member
Knuddeluff

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