Commission Rate by margin formula HELP!

geckosigns

New Member
Joined
Aug 8, 2009
Messages
2
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
Like so:

Excel Workbook
FG
61000.00Profit
724.5%Margin
8Commission
Data
 
Last edited:

phxsportz

Well-known Member
Joined
Jun 11, 2006
Messages
1,985
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

Well-known Member
Joined
Jun 19, 2008
Messages
2,465
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
 

Forum statistics

Threads
1,082,632
Messages
5,366,678
Members
400,913
Latest member
SarahMS1

Some videos you may like

This Week's Hot Topics

Top