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
- Riny_van_EekelenPlatinum Contributor
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.
- mtarlerSilver Contributor
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))
- sg2021Copper ContributorThank you for your response! I will give this a shot and update if I cannot make it a go. Thanks!
- mathetesSilver Contributor
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.
- sg2021Copper Contributor
Good morning,
Thank you for taking the time to reply to my request. Below are more for you to review. Thanks for your time!
25 x 280 full8 x 280 cuts47 x 156 full3.5 x 48.5 cuts31 x 104 full30.5 x 67.75 cuts38.5 x 182.5 full19 x 90 cuts48.5 x 144.531 x 79- mathetesSilver Contributor
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.