Working out varying price breaks - Excel Formula Help

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

RIchard
PB-Tester.xlsx
LVL 3
Richard SquibbI.T & Services ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
No file attached. I will see what I can do with data above in the meantime.
0
Richard SquibbI.T & Services ManagerAuthor Commented:
Apologies! file now attached

Thanks,

Richard
0
Rob HensonFinance AnalystCommented:
Do the columns have to stay in the order they are currently in? Can it be changed to:

Product      Min Ord            PBQ1      PBQ2      PBQ3            Price      PB1            PB2            PB3            Cases Req            Price
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Richard SquibbI.T & Services ManagerAuthor Commented:
Hi Rob,

They could be in any order really.

Thanks
0
Rob HensonFinance AnalystCommented:
See attached.
PB-Tester.xlsx
0
ShumsDistinguished Expert - 2017Commented:
Hi Richard,

Check in attached, I have added some more rows considering minimum order quantity, you can try changing any Cases Req and let me know...
Richard_PB-Tester.xlsx
0
Richard SquibbI.T & Services ManagerAuthor Commented:
Hi Shums,

Thanks for the spreadsheet. It's nearly perfect, the only issue is sometimes there is only one or two or even no price breaks.  on your spreadsheet if I remove one one of the price breaks the formula just returns the error which is blank.  Could the formula be amended in a way that is there is no price breaks or only one it would still work?

Also, Thanks for your assistance too Rob.
0
Rob HensonFinance AnalystCommented:
My suggestion already allows for fewer/no Price Breaks and covers if the Required Quantity is less than the Min Order Quantity.
0
ShumsDistinguished Expert - 2017Commented:
Richard,

If there will be no price break, what should be default price break to be considered?
0
Richard SquibbI.T & Services ManagerAuthor Commented:
Hi Rob,

Apologies so it does! teach me to look late last night.  I just need to work out how to incorporate this into my master spreadsheet now.

I'll report back after lunch

thanks again
0
Richard SquibbI.T & Services ManagerAuthor Commented:
Hi Shums,

If there is no price break the column 'Price' should be used

Thanks,

Richard
0
Rob HensonFinance AnalystCommented:
Are you able to determine what is the most number of price breaks that are likely?
0
ShumsDistinguished Expert - 2017Commented:
Richard,

Check attached...
Richard_PB-Tester_v2.xlsx
0
Rob HensonFinance AnalystCommented:
Reason why I am asking about the Maximum number of price breaks is that you could use the COUNT function to determine number of Price Breaks and then the CHOOSE function to determine which cells to compare with required quantity.

If you are able to determine that there would only be 4 options, ie base price and up to 3 Price breaks, then you would allow for 4 options in the CHOOSE function, eg:

If less than MIN then "Message" else CHOOSE(COUNT of PBs{1-4},{1} Base Price,{2} Base or PB1,{3} Base or PB1 or PB2,{4} Base or PB1 or PB2 or PB3)

With that you could keep the same layout but would have to allow the extra columns for each Price Break option, even if for a particular product the PB is blank.

See attached with the above in rows 7 - 9, ie your original layout. I have put two options, columns M or N; Price per Unit or Total Price.
PB-Tester.xlsx
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard SquibbI.T & Services ManagerAuthor Commented:
Hi Rob,

That works perfectly for me - thanks again for your time on this.

Richard
0
Richard SquibbI.T & Services ManagerAuthor Commented:
A formula using Choose and Count worked perfectly for this scenario.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.