how do I combine multiple IF statements to come up with 1 value?

[Guest]

I am trying to create an automated commission calculation sheet for my sales
team, however I can't find how to create the formula.

What I'd like to achieve is the following:

revenue:
0 - 10 ==> no commission
10.1 - 20 ==> 2.5% commission of the amount over and above 10
20.1 - 30 ==> 5% commission of the amount over and above 20
etc.

The formula I created now double counts the amount of revenue and that is
not the intention.

Thank you for your help!

 

[Trevor Shuttleworth]

=IF(A1<=10,0,IF(A1<=20,2.5,IF(A1<=30,5,....

Regards

Trevor

 

[Bill Kuunders]

Set up a table

treshold marginal rate differential rate
10000 0.025 0.025
20000 0.05 0.025
30000 0.075 0.025
40000 0.1 0.025
50000 0.125 0.025


use the formula
=SUMPRODUCT(--(D5>$A$4:$A$8), (D5-$A$4:$A$8), $C$4:$C$8)
where D5 holds the amount sold
A4 to A8 is the tresholds
C4 to C8 are the incremental %'ges

you can change the borders and or the rates in the table without having to
change formula's

for a detailed explanation go to
http://www.mcgimpsey.com/excel/variablerate.html

 

[Guest]

Assuming the amount to calculate commission on is in A1:
=IF(A1>20,0.05 * (A1-20),IF(A1>10,0.025*(A1-10),0))

You don't say what to do for over 30? I presume anything above 20 is
calculated at the 5% of amout over 20.

I guess the thing to notice is that the tests should run to test the largest
value first, then work down to a no-commission value. The first true
condition will be the one that the result is based on.

 

[Guest]

Just a word of warning on the table approach it will recalc all the
historical data linked to it if you change the table it may be better to use
a macro that pastes the value in there.

Give me your thaughts.

 

[Guest]

=LOOKUP(A1,{0,10.1,20.1},{0,2.5%,5%})

 

[Dana DeLouis]

0 - 10 ==> no commission

10.1 - 20 ==> 2.5% commission of the amount over and above 10
20.1 - 30 ==> 5% commission of the amount over and above 20

Hi. Another way...
=MAX(0,(A1-10)/40,(A1-15)/20)