price breaks based on volume and a threshold [SOLVED]

[sallan]

Hi Folks, this is driving me nuts.

[pre]

1 TO 10	         25.00
11 TO 50	 15.00
51 TO 100	 9.00
101 TO 250	 5.40

[/pre]
In this there is an area where it is cheaper to buy for example 12 instead of 10 and I am trying to work out a formula to deal with this funny step change down as people buy more.

 

[Faseeh]

Hi Sallan,


Try:

=LOOKUP(1,{1,11,51,101},{25,15,9,5.4})

You can replace the first argument i.e. 1 with any value between 1 and 250.


Regards,

 

[SirJB7]

Hi, sallan!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/price%20breaks%20based%20on%20volume%20and%20a%20threshold%20%28for%20sallan%20at%20chandoo.org%29.xlsx


Columns A:C, your price table.

Columns E:F, price per X quantity, highlighted bargains.


Conditional formula:

=Y($F2<MAX($F$1:$F1)) -----> in english: =AND($F2<MAX($F$1:$F1))


Just advise if any issue.


Regards!

 

[sallan]

Thanks for this I will take a look over the weekend


Regards


sam

 

[sallan]

Hi SirJB7


works great, many thanks


Sam

 

[SirJB7]

Hi, sallan!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!