Calculate price based on qty. & discounted at different levels

**cgalloway** · 01-13-2012, 11:50 AM

Hello Excel Forum,
I found a formula on this site (example below) that would appear to work somewhat. The problem is when someone chooses the "6" unit the price drops below the "5th" unit...
That said, what I'm trying to do is charge a set price for 1-5, however if they pick a "6th" unit the price for the first "5" should be added at the base price, with a discount for the "6" based on a different price. What this formula is doing is when you pick "6" it discounts the first six at the new price... (Does that make sense?)

Example:
=IF(A34=C1,A34*C10,IF(A34<C6,A34*D20,IF(A34<C11,A34*E20,IF(A34<C16,A34*F20,IF(A34<C21,A34*G20,IF(A34<C26,A34*H20,IF(A34<31,A34*I20,IF(A36<41,A34*J20,IF(A34<41,A34*K20)))))))))

Desire:
if someone picks "1" the price is C10, if someone picks 2-5 the price is D20, if someone picks 6-10 the price for the first "5" should remain at the price of D20, then the sixth should be calculated at E20 and added to the total of the first 5

Thank you (in advance)

**JBeaucaire** · 01-13-2012, 11:53 AM

You're wanting tiered pricing. Each tier adds to the next, it does not discount retroactively into prior tiers.

This formula is a little hard to read, but it basically charges full price for all items, then discounts back off a portion for each change from tier to tier. This would be easier to show you if you gave us a workbook showing your tiered prices.

'TIERED SEQUENTIAL PRICING (LAST {} IS THE DIFFERENTIAL BETWEEN TIERS)

=SUMPRODUCT(--(A1>{0,10,50,100}),A1-{0,10,50,100},{10,-2,-2,-1})

In this formula, all items are charged the initial $10. Then all items from 10+ are given the first discount of -$2. Then all items above 50 are given an additional -$2 off. Finally, everthing above 100 are given another final -$1 discount.

---------------
=SUMPRODUCT(--(A1 > {0,801,1501,4001,7501}), A1 - {0,801,1501,4001,7501}, {0.15,0.05,0.02,0.03,0.05})

In this formula we're getting a discount percentage instead of a price. All items are given the inital 15%, then items above 800 get an additional 5%, above 1500 then get an addition 2%, above 4000 get 3% more, and above 7500 get one more 5% discount.

Commission sample
http://www.excelforum.com/attachment...ple-tiers-.xls

Here's a sample file that shows you an example of TIERED discounting structure:
Complex vs Simple discount techniques