Pro rata calclation

[Guest]

I am formulating a discount structure for my dealers.
I want to do prorata calculation for the same on excel.
There are two limits which have been defined. The lowest limit is Rs 300000
for which the dealers get a discount of 3%. The highest limit is 2000000 for
which the discount is 5%.
I would like to have a framework on excel in which between 300000 and
2000000, discount is allocated on pro rata basis. Currently I am using Excel
2000
Sales: 300000 - Discount 3%
Sales: 2000000 - Discount 5%

 

[Bernard Liengme]

With Sales in A1:
a) compute percentage with =IF(A1>=2000000,5%,IF(A1>=300000,3%,0%)) format
cell as percent
or
b) compute discount with =IF(A1>=2000000,5%,IF(A1>=300000,3%,0%))*A1 format
cell as currency
or
c) compute discounted price with
A1-IF(A1>=2000000,5%,IF(A1>=300000,3%,0%))*A1

OR do you mean the discount varies linearly from 3% to 5% as sales go from
300,000 to 2,000,000? In which case the discount is found with
=MIN(IF(A1>=300000,(A1-300000)*0.0000000117647+0.03,0),5%)

best wishes