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!