Commission Rate by margin formula HELP!

[geckosigns]

Hello,

I'm having a heck of a time figuring out how i can set up my commission rate in excel for my company. Our scale is as follows:

Margin % is in cell F7
Profit Dollar amount is in cell F6

under 20% margin pay 15% commission on profit
21% to 30% margin pay 20% commission on profit
31% to 40% margin pay 25% commission on profit
41% to 50% margin pay 30% commission on profit
over 50% margin pay 35% commission on profit

Any help would be greatly appreciated!

Thanks

 

[jbeaucaire]

Like so:

Excel Workbook
	F	G
6	1000.00	Profit
7	24.5%	Margin
8	200.00	Commission
Data

 

[phxsportz]

Probably a cleaner way of doing this... but .. it seems to work

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:114px;" /><col style="width:87px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >E</td><td >F</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Profit Amount</td><td style="text-align:center; ">$100.00</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Margin %</td><td style="text-align:center; ">50.00%</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ffcc99; font-weight:bold; text-align:center; ">Commission</td><td style="background-color:#ccffcc; text-align:center; ">$30.00</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F8</td><td >=F6*IF(F7<0.21,0.15,IF<span style=' color:008000; '>(AND<span style=' color:#0000ff; '>(F7>0.2,F7 <0.31)</span>,0.2,IF<span style=' color:#0000ff; '>(AND<span style=' color:#ff0000; '>(F7>0.3,F7<0.41)</span>,0.25,IF<span style=' color:#ff0000; '>(AND<span style=' color:#804000; '>(F7>0.4,F7<0.51)</span>,0.3,0.35)</span>)</span>)</span>)</td></tr></table></td></tr></table>

 

[wsjackman]

Hello,

I'm having a heck of a time figuring out how i can set up my commission rate in excel for my company. Our scale is as follows:

Margin % is in cell F7
Profit Dollar amount is in cell F6

under 20% margin pay 15% commission on profit
21% to 30% margin pay 20% commission on profit
31% to 40% margin pay 25% commission on profit
41% to 50% margin pay 30% commission on profit
over 50% margin pay 35% commission on profit

Any help would be greatly appreciated!

Thanks

maybe
=choose(median(2,f7*10-.001,5),,.15,.2,.25,.3,.35)*f6

 

[geckosigns]

AWESOME!

Thanks

It Works!