Forum Discussion
best formula for quantity breaks
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.
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,
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.
Riny_van_Eekelen Hi Riny, unlocked the price file for you just in case you needed to access.
regards
- 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