Forum Discussion
best formula for quantity breaks
HI Team,
I would love it if you could provide some valuable assistance, I have a Price list with QTY break and we have upto 4 x qty breaks (or none) by each product code comes with a different price and variable QTY break number, SO what we i am looking to acheive is for the sales team to type in a qty and it picks up the QTY break $$ value once its hit, i.e if hits 50 it defaults to $6.56 as per image i have tried Vlookup, Match, IF and Vlookup came up with best results but would zero value is there where no other qty breaks, i have screen shot so hopefully you can make sense of my issue
Look forward to your guidance in due course
Marcel55 Thanks for the file. On opening it warned for a circular reference in G11, an overly complicated formula to determine (as I understand) to calculate the overall discount from the table towards the right.
I have added a sheet to my original file and mover the pricing table into it, as well as the lay-out of the quote from your file. The (structured) pricing table can sit anywhere in the workbook, so you can move it to its own sheet, without having to rewrite the formulae in the quote.
For one product (cc7r), I demonstrated how the Net Price can be calculated, using the formula from my initial file. Then the discount is no more than the difference between the Trade Price and the Net Price.
Important, though, that the pricing table is in the format as in my example. A product code, the Qty Level brackets, Pricing information and then some columns at the end that calculate the price difference from each level to the previous level. All grouped together. I would have done that for you you but your pricing table was protected.
Attaching my file.
10 Replies
Marcel55 Just for clarification. What if (in the first row of your example) the Qty is 30. Would all go at the price for the "20-bracket" (i.e. $6.86) or would the first 19 go for the regular price $7.15 and the next 11 for $6.86?
Riny_van_Eekelen Hi Riny, Thanks for your response, the pricing will be all based on once you hit the 20 then the price changes for all 1 - 19 = $7.15, then 20 to 49 for example then $6.86 an so on..as we sell box qty we incentivise for this, hope this helps, rgds
Marcel55 Not sure that the set up of your schedule is suited for a solution I have in mind, but attaching a workbook anyhow. You need a tiered calculation of a price, but perhaps it helps you get started.
You would need a "pricing table" for each and every product, though.
