Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for determining sales commission
I pay sales commission that varies based on profit margin
I need a formula based on the margin: When margin is .4, sales commission = .1 When margin is between .35 and ..39, sales commission = .09 ETC What is the formula to determine? I got this far: =IF((H39=0.4),G39*0.1) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for determining sales commission
Try
=G39*VLOOKUP(H39,{0,0.11;0.35,0.1;0.4,0.09},2,TRUE ) the values, like .11 range between 0 and .35, .1 between .35 and .4 etc. Change to suit. -- HTH RP (remove nothere from the email address if mailing direct) "Doug" wrote in message ... I pay sales commission that varies based on profit margin I need a formula based on the margin: When margin is .4, sales commission = .1 When margin is between .35 and ..39, sales commission = .09 ETC What is the formula to determine? I got this far: =IF((H39=0.4),G39*0.1) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for determining sales commission
You could continue to build out the IF statement:
=if(h39=.4,.1,if(h39=.35,0.9,0))*g39 and so on. I'd suggest instead building a two-column table. The first column would have the margin breakpoints and the second column the payout rates. The first column MUST be increasing. So, if your rates were 0 up to .35, .09 up to .4 and .1 for .4 and up, your table would be 0 0 in row 1, .35 .09 in row 2 and .4 .1 in row 3. And your calculation would be =if(h39<0,0,g39*vlookup(h39,Table,2,true)), where Table is defined as the range where your table lives. Then if you add more breakpoints, you only need to update the table (but always be sure that the breakpoints in the first column are in ascending order). --Bruce "Doug" wrote: I pay sales commission that varies based on profit margin I need a formula based on the margin: When margin is .4, sales commission = .1 When margin is between .35 and ..39, sales commission = .09 ETC What is the formula to determine? I got this far: =IF((H39=0.4),G39*0.1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sales V Commission updated calculation | Excel Discussion (Misc queries) | |||
Re-arrangement | Excel Discussion (Misc queries) | |||
commission report formula | Excel Discussion (Misc queries) | |||
Looking for a temlate for sales and commission input? | Excel Discussion (Misc queries) | |||
sales commission template | Excel Discussion (Misc queries) |