Creating formula for a commission statement

[Alice151]

I am trying to create a commission statement for an employee who will earn increasing commission rates as they bill more. I'd like to be able to plug the amount of revenue they bring in (which will be updated each month) to automatically work out their earnings. If they bring in revenue between £0 - £99,000 they earn 33%; if they bring in revenue between £99,001 - £154,000 they earn the initial 33% up to £99,000 and then 38% on the rest and so on up to 53% (see bands below). How would I calculate the amount within each band?

1 99,000 33.00% = A
99,001 154,000 38.00% = B
154,001 198,000 43.00% = C
198,001 308,000 48.00% = D
308,001 374,000 50.50% = E
374,001 2,200,000 53.00% = F
Total commission earned = G

E.g. If someone brings in £200,000 in revenue, then I'm hoping to get the following:
A = 32,670
B = 20,899.62
C = 18,919.57
D = 959.52
E= (blank)
F = (blank)
G = 73,448.71

Hopefully this makes sense and someone knows the answer!!

[arthurbr]

Hi and welcome to the board

The attached contains a solution for which you first have to build a little table which is self explanatory.
Is that what you want ?

There is a small difference, trying to find why

[Alice151]

Aha! Thanks-you yes that seems to work - can you tell me what this type of calculation is called?

I wonder if part of the slight discrepancy is because on my bands, they would start 99,001; 154,001 etc (so one extra), but I tried changing your worksheet and it changed the total to 73,449.85 so it's still slightly out..

Thank-you so much!

[arthurbr]

It doesn't have a particular name that I know of.I call it a "differential rates" technique