Incentive Calculation Formula

[Gemcorp_Recycling]

HI 

Can any one help me with creating a formula for below type of incentive structure.

Sl. No Transaction slab Amount in Rs Amount in Rs
1 0 - 20,000 100
2 20,001 - 40,000 100
3 40,001 - 60,000 100
4 60,001 - 80,000 100
5 80,001 - 100,000 100
and so on
For every lakh transaction Rs 100 additional

For Example - 

If the transaction amount is Rs 40,100 then the incentive would be Rs 300/-
If the transaction amount is Rs 1,01,00 then the incentive would be Rs 700/-

Month wise Turnover Incentive scheme:

For 10 Lakh A Month - 2,000/- Additional Incentive

For 15 Lakh A Month - 3,500/- Additional Incentive
For 25 Lakh A Month - 7,000/- Additional Incentive

[#2 · Accepted solution]

@Gemcorp_Recycling 

With a transaction amount in T2:

=CEILING(T2,20000)/200+QUOTIENT(T2,100000)*100+LOOKUP(T2,{0,1000000,1500000,2500000},{0,2000,3500,7000})

This can be filled down.

[#3]

Thanks a lot for quick response.