Calculating sales commission that changes based on a sales volume

[Elbowes]

I have a 7 tiered sales volume grid and the commission paid per thousand
changes at each tier. EG:

0 - 1.5M $3.00 per K
1.6 - 2.5 M $4.00 per K
2.6 - 4.0 M $5.00 per K
and so on - for 7 tiers worth of numbers - the last one being at $10 per K.

What formula and/or function can I use to calculate the correct amount of
commission, at the correct tier level knowing that a payment could include
commissions from 2 consecutive levels.

Each year a sales agent remains under contract, the next year's grid changes
slightly for that person in that the rate per $K skips a level giving that
agent more money. (Eg at year 2, pay $4 for both tier 1 & 2, then year 3 -
pay $5 for tiers 1, 2 & 3).

I also have several sales reps to keep track of using the same graduated
volume grid.
Thank you for any help you can provide.

 

[Pete_UK]

Follow this link:

http://www.mcgimpsey.com/excel/variablerate.html

for some worked examples.

Hope this helps.

Pete

 

[Elbowes]

At first glance - it looks like it meets most of my requirements. I'll have
a go at it and hopefully I won't need to disturb you again.
Thank you very much and regards,