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!

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

Regards

Trevor


"Johanna" wrote in message
...
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!

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



--
Greetings from New Zealand

"Johanna" wrote in message
...
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!

Assuming the amount to calculate commission on is in A1:
=IF(A120,0.05 * (A1-20),IF(A110,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.


"Johanna" wrote:

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!

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Bill Kuunders" wrote:

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



--
Greetings from New Zealand

"Johanna" wrote in message
...
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!

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


"Johanna" wrote:

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!

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)

--
HTH :)
Dana DeLouis
Windows XP & Office 2007

"Johanna" wrote in message
...
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!