Calculate a quantity rebate

[Sige]

Hi there,

I would like to calculate the discount or penalty.
Do you have an elegant way?

A simplified "Discount" table:

From To Discount

1 1 1000 lumpsum penalty
2 50 500 lumpsum penalty
51 100 250 lumpsum penalty
101 150 0
151 200 -1% discount on Turnover
201 250 -2%
251 ... -3%
....
100.000 999.999 -5%

If my volume is in a certain bracket I need to calculate a percentage
on the turnover if it is worth a discount or return the lumpsum
penalty.

Thanks for your insights,
Sige

 

[Sige]

My solution looks like:

=IF(LOOKUP(B2;Rebates)<0;LOOKUP(B2;Rebates)*A2;LOOKUP(B2;Rebates))