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!