Forum Discussion
Material Leftovers
Hello,
I have possibly an unusual Formula request, but necessary for our company. We have material that is being cut and would like to see if there is a formula that I can use to figure out how many piece I would be able to gain out of one piece.
For example, I would need to enter in original size of 31x104 and will need to cut pieces to the size of 30.5x67.75. Is there a formula that tells me how many pieces I can get out of this 31x104 piece?
Thanks for your help!
13 Replies
sg2021 Now, I'm not a mathematician, so there could be more intelligent ways to resolve this. But, the following formula will do the trick.
=MAX(INT(MatW/ProdW)*INT(MatL/ProdL),INT(MatW/ProdL)*INT(MatL/ProdW))MatW, ProdW, MatL and ProdL are named ranges, each referring to the Material's and Product's Width and Length. Dividing Widths and Lengths in both "directions" will enable you to determine the maximum amount of Product that can be cut out the Material. A working example is attached.
sg2021 I liked Riny_van_Eekelen approach but thought we could do a bit better in some cases where the left over has enough scrap to rotate is and cut additional product at a 90 degree from the originals. Here is the updated formula and sheet. You'll notice the formula gets a bit longer but it is all based on Riny's orginal formula and named ranges:
=MAX(INT(MatW/ProdW)*INT(MatL/ProdL)+INT(MOD(MatW,ProdW)/ProdL)*INT(MatL/ProdW)+INT(MOD(MatL,ProdL)/ProdW)*INT(MatW/ProdL),INT(MatW/ProdL)*INT(MatL/ProdW)+INT(MOD(MatW,ProdL)/ProdW)*INT(MatL/ProdL)+INT(MOD(MatL,ProdW)/ProdL)*INT(MatW/ProdW))- Thank you for your response! I will give this a shot and update if I cannot make it a go. Thanks!
Unless I'm missing something, the answer in your example is 1. Given those dimensions, there'll be some scrap, but certainly not enough for another piece of the same size.
So maybe that's not a good example.
Are there other examples that would make it worthwhile to come up with a formula? And, if possible, could you include a spreadsheet that would give an idea of the range of material sizes you're dealing with and how you've got it laid out.
I'm pretty sure that the solution you got from Riny_van_Eekelen fully meets your need, so unless you come back with a request for further help, I'll defer to him.
