Tiered Commission Formula

[sarahabb]

Hi Board,

I'm trying to create a formula to calculate a tiered commission schedule. The schedule is:
50% for up to $10,000
60% for $10,001-$15,000
70% above $15,000

So far I have figured out a formula that calculates all the commissions but I can't get it to stop taking the 50% after 10,000 or the 60% after 15,000. Please help!

thanks!
Sarah

 

[SteveO59L]

=if(a1>15000,a1*1.7,if(a1>10000,a1*1.6,a1*1.5))

 

[sarahabb]

Thanks for the response Steve, but that formula isn't working for me...

 

[West Man]

Try: =(A1>15000)*0.7*A1+(AND(A1>10000,A1<15001)*0.6*A1)+(A1<10001)*(0.5*A1)

 

[sarahabb]

Thanks West Man but something is still off... it is not calculating properly.

 

[MrKowz]

Try:

=A1*LOOKUP(A1,{0,10001,15000},{0.5,0.6,0.7})

This worked on my end.

 

[sarahabb]

Mr Kowz,

Very interesting formula but I can't get it to work on my end. For example, let's say total sales were $20,000. When I use that formula, the commission comes out to $14,000 but when I do the calculations myself, the commission should be $11,500.

$5000 for the 50% up to $10000
$3000 for the 60% of $15,000-$10,000
$3500 for the 70% of $20,000-$15,000

Does that make sense? I still have no idea what to do...

 

[West Man]

Only quickly tested but try =A1*0.5+((A1>10000)*(A1-10000)*0.1)+((A1>15000)*(A1-15000)*0.1)

 

[sarahabb]

It works! Thanks so much West Man!

 

[jamiew01]

Hi, I have a similar issue but cannot get the final formula adopted onto my scheme...

my comms structure has a threshold where no commission is paid, see below...

0-40000 - ZERO%
40001-100000 - 20%
100001-200000 - 25%
200001-300000 - 30%
300001-350000 - 40%
350001+ - 50%

E.G. if the sales were 250,000, the final figure would be:

40001-100000 = 12000
100000-200000 = 25000
200001-250000 = 15000
TOTAL 52,000

The field in question is F23 - any suggestions???

Many thanks in advance!