Tiered Charging/Allocation

[sanjkn]

Hi, hoping someone can help with this, i'm trying to create a formula that will allow me to calculate a charge per licence within a structured tier, eg for 152,000 licences, the first 50k licences would be at 0.15p, the next 50k would be at 0.10p, the next 50k would be at 0.08p and the balance would be at the next tier etc?


Lower Limit Upper Limit Monthly Licence Charge
0 50,000 0.150
50,001 100,000 0.100
100,001 150,000 0.080
150,001 200,000 0.080
200,001 250,000 0.060
250,001 350,000 0.060
350,001 500,000 0.020
500,001 1,000,000 0.010
1,000,001 1,500,000 0.010
1,500,001 2,000,000 0.008
2,000,001 0.008

Thanks in advance

[Glenn Kennedy]

Something like this??

=SUMPRODUCT(--(A13>{0,50000,100000,200000,350000,500000,1500000}),--(A13-{0,50000,100000,200000,350000,500000,1500000}),{0.15,-0.05,-0.02,-0.02,-0.04,-0.01,-0.002})

[sanjkn]

Perfect, Thank you

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.