asked on

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

Rob Henson

No file attached. I will see what I can do with data above in the meantime.

Richard Squibb

ASKER

Apologies! file now attached

Thanks,

Richard

Rob Henson

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

Richard Squibb

ASKER

Hi Rob,

They could be in any order really.

Thanks

Rob Henson

See attached.
PB-Tester.xlsx

Shums Faruk

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

Richard Squibb

ASKER

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.

Rob Henson

My suggestion already allows for fewer/no Price Breaks and covers if the Required Quantity is less than the Min Order Quantity.

Shums Faruk

Richard,

If there will be no price break, what should be default price break to be considered?

Richard Squibb

ASKER

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

Richard Squibb

ASKER

Hi Shums,

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

Thanks,

Richard

Rob Henson

Are you able to determine what is the most number of price breaks that are likely?

SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION