Formula needed for multi level discounts for seperate blocks

**neal1079** · 07-05-2012, 02:37 PM

I am stuck and cannot figure out how to setup a formual that will allow me to discount a price based on multiple criteria. Now the kicker is that a price can be eligible for multiple discounts. Here is an example we have setup up pricing and we state that the minimum has to be $10,000. If the minimum is met then the first block of $2000 that is above the minimum will be discounted 10% (basically anything from $10,001-12,000). Then if that is met we will then apply an another discount to the next block of $2000 which will be a 15% discount (anything from $12,001 - $14,000). Then anything above $14,000 will be given a 20% discount.
I have been able to get that the 20% discount by subtracting the Total from 14,000 and multiply the answer by .20. But how can i get the remaining blocks to apply a discount only for each level of $2000. I cannot just multiply the discount to 2,000 because there may be cases where they have only purchased 11,050 which would mean i could only discount 1,050 with a 10% discount.

Thank you in advance,

Neal

**Ace_XL** · 07-05-2012, 02:52 PM

Crude try to start with...

=IF(A1<10000,0,IF(A1<12000,MIN(A1-10000,2000)*0.1,IF(A1<14000,MIN(A1-12000,2000)*0.15+(2000*0.1),(A1-14000)*0.2+(2000*0.15)+(2000*0.1))))

**neal1079** · 07-05-2012, 03:45 PM

Thank you very much, I would have never thought to throw the MIN statement with the IF statement. I can also use this and cut it down by percentages to show exactly how much discount will be given by block.

**Ace_XL** · 07-05-2012, 04:10 PM

great..enjoy the forum. Please mark the thread as solved

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save