Formula for determining sales commission

[Guest]

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)

 

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

 

[Guest]

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