Formula for determining sales commission

D

Doug

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)
 
B

Bob Phillips

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)
 
B

bpeltzer

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top