best formula for quantity breaks

Occasional Contributor

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

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 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.

@Riny_van_Eekelen Hi Riny, many thanks, yes on refection it needs to be a table for sure, as i did build a table to to try and solve this problem, issue i have is this file is looking at another price file and changing Qty break by product via vlookup hence the variable qty its not calculating off the first value unfortunately, i apologise i was not clear, i have highlighted the cells which come from the price list, i was just going to get our team to overide the price manually to get the price break value but thought it better if they can just add the qty in and once hit the QB then it adjusts to meet that price accordingly but as it changes by product it made it tough to calculate, be nice if we just sold pencils not sure if this makes sense and i do appreciate your input




@Marcel55 Can't really tell why the VLOOKUP in your screenshot returns a #REF! error and don't understand what you mean by "hence the variable qty its not calculating off the first value". It would be helpful if you could give us a complete picture, including the structure of the price list and how it should tie in to the schedule in the screenshot.


Anyhow, I transformed my original table and formula to something that may come closer to your needs. It is using a structured Pricing table for all products. It finds the correct row for the Product in the table and then applies the same formula to transposed ranges for "lower" and "delta", based on the relevant product row. And there is no need for all the calculations in your columns K through V.


The formula in C14 is using helper ranges to demonstrate the principle. The cell below produces the same result in an "all-in-one" formula. If you have a modern Excel version you can further optimise this formula with LET as it has quite a few repetitive calculations and references.

@Riny_van_Eekelen Thanks Riny,

The Ref errors where shown as i deleted the Tables that i created to test and the variable statement was more around the fact the numbers all change based on different products sorry, i certainly appreciate your patience on this and i will give this new version a go and let you know, certainly starting to like Xcel more now but it does challenge us sometimes thats for sure appreciate your support.

@Marcel55 Hi Riny

I have attached the file so you can see all the workings if that helps and no doubt will explain much better than i can, this has two tabs one is the price file and the other is the quote sheet, 


best response confirmed by Hans Vogelaar (MVP)

@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. 



Thanks Riny again thanks for your efforts and patience here the file looks good and will certainly make my work much easier and better to work with, i will ensure we keep the format for sure regards

@Riny_van_Eekelen Hi Riny, unlocked the price file for you just in case you needed to access.