I'm having trouble creating an Excel formula which looks at different price breaks for products and then returns the correct value. If you see this grid below: (file also attached)
Product Price Min Ord PBQ1 PB1 PBQ2 PB2 PBQ3 PB3 Cases Req Price
LW4040 £100.00 5 10 £90.00 20 £80.00 30 £70.00 21
LW5050 £200.00 15 20 £190.00 30 £180.00 40 £170.00 19
LW6060 £300.00 25 30 £290.00 40 £280.00 50 £270.00 50
Whatever I enter in the Cases required field I would like the formula to work out what price to enter in the price field. The PBQ (price break quantity) field may be different each row and also may not have any data in in at all.
So for product LW4040 if cases required was 9 or less, enter £100, if cases required were between 10-19 enter £90, if cases required were 20-29 enter £80 and if 30 or over enter £70.
The main issue I am having is that the price breaks can be different each time, and sometimes only have one price break.
Many Thanks in advance