Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Kunal Khanna
 
Posts: n/a
Default Pro rata calclation

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%

  #2   Report Post  
Bernard Liengme
 
Posts: n/a
Default

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


--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Kunal Khanna" <Kunal wrote in message
...
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%



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 12:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"