Tiered Pricing formula

**ahmedrz81** · 10-25-2018, 03:23 AM

Hi Guys,
I am trying to create a tiered discount rate for our distributors.
it goes like this:

for 0-300 cartons 0 discount
301-700 3
701 - 1200 6
1201- 1800 8
1800 onward 10

This means that if someone sells 1300 units they get 0 discount for first 300, 6 for next 399, 8 for next 499 and so on. and then add the total discount they get to one cell.
What would be the way to go about this?
Really appreciate your help

**Glenn Kennedy** · 10-25-2018, 03:45 AM

Your Q is ambiguous. When you say "6 for next 399" do you mean 6 for EACH of the next 399.... or 6 irrespective of whether you sell 1 or 399?

**ahmedrz81** · 10-25-2018, 04:48 AM

6 on every unit i.e 6*399. Basically discount has to be applied on every carton based on the range stated in the question. As soon as they buy 301 carton the additional carton would have a discount of 3 same for units upto 700 after which 6 discount will be given.
Hope that explains and thanks for the reply!

**Sam Capricci** · 10-25-2018, 07:03 AM

are you saying that for 301 - 700 they get 3 then 701 - 1200 they get 6 or they get 9, 3 for the first 700 and 6 for those below 1200 but above 700? Because depending on the answer there are two different formulas and results.
this is one way for the first... =LOOKUP(A1,{0,301,701,1201,1801},{0,3,6,8,10})
but a sumproduct or something else like an adjustment to this lookup would be the formula for the second.

And, if you want to cover any issue with blanks then... =IF(A1="","",LOOKUP(A1,{0,301,701,1201,1801},{0,3,6,8,10}))
And in both instances point the formula to where your data is by changing A1 to your data.

**Glenn Kennedy** · 10-25-2018, 12:09 PM

Hi. I was away...

=SUMPRODUCT(--(A2>{0,300,700,1200,1800}),--(A2-{0,300,700,1200,1800}),{0,3,3,2,2})

explanation of : {0,3,3,2,2}

This is the DIFFERENCE between the discount at each tier level.

First tier 0
Second tier 3... difference = 3
Third tier 6... difference = 3
Fourth tier 8... difference = 2
etc....