Hi
I would like to apply a discount scale to an amount.
Discount scale;
0 - 50,000 0
50,000 - 100,000 -10%
100,000 - 250,000 -25%
250,000 + -40%
The scale is progressive so if I had an amount of 1,000,000 I would receive a 10% discount on the 50 - 100k and a 25% discount on the 100 - 250k and 40% discount of the remaining 750k.
Any help would be much appreciated.
Many thanks
Try:
=SUMPRODUCT(--(D1>{50000;100000;250000}),(D1-{50000;100000;250000}),{0.1;0.15;0.15})
Where D1 contains the original value.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Assuming your amount in A6:
=IF(A6>250000,0.4*A6-57500,IF(A6>100000,0.25*A6-245000,IF(A6>50000,0.1*A6-5000,0)))
Quang PT
Quang, that doesn't do progressive (cumulative) discounting based on ranges....
Why not, I 've checked and it get same result with youOriginally Posted by NBVC
I apologize, I misread your formula....
Try this
=SUMPRODUCT(--(A1>{0;50000;100000;250000}),--(A1-{0;50000;100000;250000}),{0;0.1;0.15;0.15})
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Africa 
Register To Reply
Bookmarks