Material Leftovers

%3CLINGO-SUB%20id%3D%22lingo-sub-2130052%22%20slang%3D%22en-US%22%3EMaterial%20Leftovers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2130052%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20possibly%20an%20unusual%20Formula%20request%2C%20but%20necessary%20for%20our%20company.%26nbsp%3B%20We%20have%20material%20that%20is%20being%20cut%20and%20would%20like%20to%20see%20if%20there%20is%20a%20formula%20that%20I%20can%20use%20to%20figure%20out%20how%20many%20piece%20I%20would%20be%20able%20to%20gain%20out%20of%20one%20piece.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20I%20would%20need%20to%20enter%20in%20original%20size%20of%2031x104%20and%20will%20need%20to%20cut%20pieces%20to%20the%20size%20of%2030.5x67.75.%26nbsp%3B%20Is%20there%20a%20formula%20that%20tells%20me%20how%20many%20pieces%20I%20can%20get%20out%20of%20this%2031x104%20piece%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2130052%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2130599%22%20slang%3D%22en-US%22%3ERe%3A%20Material%20Leftovers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2130599%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F965384%22%20target%3D%22_blank%22%3E%40sg2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnless%20I'm%20missing%20something%2C%20the%20answer%20in%20your%20example%20is%201.%20Given%20those%20dimensions%2C%20there'll%20be%20some%20scrap%2C%20but%20certainly%20not%20enough%20for%20another%20piece%20of%20the%20same%20size.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20maybe%20that's%20not%20a%20good%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20there%20other%20examples%20that%20would%20make%20it%20worthwhile%20to%20come%20up%20with%20a%20formula%3F%20And%2C%20if%20possible%2C%20could%20you%20include%20a%20spreadsheet%20that%20would%20give%20an%20idea%20of%20the%20range%20of%20material%20sizes%20you're%20dealing%20with%20and%20how%20you've%20got%20it%20laid%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2131089%22%20slang%3D%22en-US%22%3ERe%3A%20Material%20Leftovers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2131089%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F965384%22%20target%3D%22_blank%22%3E%40sg2021%3C%2FA%3E%26nbsp%3BNow%2C%20I'm%20not%20a%20mathematician%2C%20so%20there%20could%20be%20more%20intelligent%20ways%20to%20resolve%20this.%20But%2C%20the%20following%20formula%20will%20do%20the%20trick.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMAX(INT(MatW%2FProdW)*INT(MatL%2FProdL)%2CINT(MatW%2FProdL)*INT(MatL%2FProdW))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BMatW%2C%20ProdW%2C%20MatL%20and%20ProdL%20are%20named%20ranges%2C%20each%20referring%20to%20the%20Material's%20and%20Product's%20Width%20and%20Length.%20Dividing%20Widths%20and%20Lengths%20in%20both%20%22directions%22%20will%20enable%20you%20to%20determine%20the%20maximum%20amount%20of%20Product%20that%20can%20be%20cut%20out%20the%20Material.%26nbsp%3BA%20working%20example%20is%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2136760%22%20slang%3D%22en-US%22%3ERe%3A%20Material%20Leftovers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2136760%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20morning%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20taking%20the%20time%20to%20reply%20to%20my%20request.%26nbsp%3B%20Below%20are%20more%20for%20you%20to%20review.%26nbsp%3B%20Thanks%20for%20your%20time!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E25%20x%20280%20full%3C%2FDIV%3E%3CDIV%3E8%20x%20280%20cuts%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E47%20x%20156%20full%3C%2FDIV%3E%3CDIV%3E3.5%20x%2048.5%20cuts%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E31%20x%20104%20full%3C%2FDIV%3E%3CDIV%3E30.5%20x%2067.75%20cuts%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E38.5%20x%20182.5%20full%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E19%20x%2090%26nbsp%3B%20cuts%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E48.5%20x%20144.5%3C%2FDIV%3E%3CDIV%3E31%20x%2079%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2136764%22%20slang%3D%22en-US%22%3ERe%3A%20Material%20Leftovers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2136764%22%20slang%3D%22en-US%22%3EThank%20you%20for%20your%20response!%20I%20will%20give%20this%20a%20shot%20and%20update%20if%20I%20cannot%20make%20it%20a%20go.%20Thanks!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2136766%22%20slang%3D%22en-US%22%3ERe%3A%20Material%20Leftovers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2136766%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F965384%22%20target%3D%22_blank%22%3E%40sg2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20pretty%20sure%20that%20the%20solution%20you%20got%20from%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%20fully%20meets%20your%20need%2C%20so%20unless%20you%20come%20back%20with%20a%20request%20for%20further%20help%2C%20I'll%20defer%20to%20him.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2137036%22%20slang%3D%22en-US%22%3ERe%3A%20Material%20Leftovers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2137036%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F965384%22%20target%3D%22_blank%22%3E%40sg2021%3C%2FA%3E%26nbsp%3B%20I%20liked%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bapproach%20but%20thought%20we%20could%20do%20a%20bit%20better%20in%20some%20cases%20where%20the%20left%20over%20has%20enough%20scrap%20to%20rotate%20is%20and%20cut%20additional%20product%20at%20a%2090%20degree%20from%20the%20originals.%26nbsp%3B%20Here%20is%20the%20updated%20formula%20and%20sheet.%26nbsp%3B%20You'll%20notice%20the%20formula%20gets%20a%20bit%20longer%20but%20it%20is%20all%20based%20on%20Riny's%20orginal%20formula%20and%20named%20ranges%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMAX(INT(MatW%2FProdW)*INT(MatL%2FProdL)%2BINT(MOD(MatW%2CProdW)%2FProdL)*INT(MatL%2FProdW)%2BINT(MOD(MatL%2CProdL)%2FProdW)*INT(MatW%2FProdL)%2CINT(MatW%2FProdL)*INT(MatL%2FProdW)%2BINT(MOD(MatW%2CProdL)%2FProdW)*INT(MatL%2FProdL)%2BINT(MOD(MatL%2CProdW)%2FProdL)*INT(MatW%2FProdW))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2137069%22%20slang%3D%22en-US%22%3ERe%3A%20Material%20Leftovers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2137069%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerfect.%26nbsp%3B%20Thank%20you%20for%20the%20addition.%26nbsp%3B%20I%20will%20give%20this%20a%20shot%20and%20update%20if%20something%20goes%20amidst.%26nbsp%3B%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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!

14 Replies

@sg2021 

 

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.

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

@mathetes 

 

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 full
8 x 280 cuts
 
47 x 156 full
3.5 x 48.5 cuts
 
31 x 104 full
30.5 x 67.75 cuts
 
38.5 x 182.5 full 
19 x 90  cuts
 
48.5 x 144.5
31 x 79
Thank you for your response! I will give this a shot and update if I cannot make it a go. Thanks!

@sg2021 

 

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.

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

@mtarler 

 

Perfect.  Thank you for the addition.  I will give this a shot and update if something goes amidst.  Thanks!

@mtarler

 

What about the below? Doesn't seem like I can really get 39 pieces out of this material. Can you please confirm this formula works for this scenario? Thanks!

        W   L
Mat 47 156
Prod 3.5 48.5
Max 39
Max/Rotation 39

 

@sg2021 Since the Max and the Max/Rot are the same you know a solution is with all them in same direction.  The product L x 3 = 145.5 which is < 156 and then the product W x 13 = 45.5 is < 47 so you can get a grid of 3 x 13 (39) product out of that material all in the same direction.

 

BTW I wanted to mention that my solution is not the optimized solution in all cases.  It will catch some cases that Riny's orig solution didn't but not all.  For example if you have a square piece 48"x48" (1/2 sheet of plywood) and you want product that is 23"x25" (not counting loss from cuts) then the original equation would say 2, my improved version 3, but you could get 4 if you cut each piece from a corner of the material and rotate 90deg each time.

This is noted and appreciate the feedback.

Can you maybe tell me why others see a #Value instead of the answer? I see the answer on my end, but when shared through office 365 they sometimes see a #value instead of the number the formula provides. Thoughts?

W L
Mat 48.5 144.5
Prod 30.5 67.75
Max #VALUE
Max/Rotation #VALUE

@mtarler 

 

I figured out the #value issue, but now have another request that needs to be added into this formula.

 

How do I add a .03" blade cut for each value?  For example, original piece would be 19x36 and need a piece that is 13x16 with a .03" adder for the cut itself?

That's a tricky one: the most reliable way or simplest way to account for it is to add .06" to each dimension of the sizes of the desired end pieces. I say most reliable, if that level of precision is needed, and you can accept that there might occasionally be one or two fewer end pieces. Otherwise you'd have to somehow incorporate into the calculation how many of the cuts could be made on an outside (not subject to cutting) edge.
So I guess part of this whole project: is the value of the basic material such that it warrants absolute attention to the minimization of the amount of scrap.
Is the material involved such that scrap can be re-worked, melted, smelted, whatever, into new original pieces? If so, you might want to factor in here the cost of re-working material and the cost of precision cutting.
In short, there are (or may be) considerations other than the math we've dealt with so far in just maximizing the number of end pieces that can be cut from a single original piece.

@sg2021 as @mathetes  said, the easiest way is to add it to the product size dimensions and an easy way to do that is using the NAMES defined by @Riny_van_Eekelen .  So in the Name Manager just add 0.03 to them there.

mtarler_0-1613436845051.png

Out of curiosity, what was that #Value issue you were having and how did you solve it?

I tried to attach the file but it is just getting stuck when I try.  But you can see what I did in the image above.

سلام عليكم ورحمة الله وبركاته مناشده لااهل الخير ولادتي بحاجه الى جهاز وجد لوجه الله تعالى أرجوكم خوانيو تساعد في وضع محرج للغايه داخل على الله ثم أنقذوا ولدتيي ياناس اتقو الله فينا حرام عليكم أمي مش قادره تاخد نفس ومحتاجه جهاز الأكسجين ضروري مشان الله أنا ما قدرتي اجيب جهاز انا وامي حرمتين ومالنا موعين غير الله أرجوكم ساعدونا الي بس تاخد نفس اغيثوتنا 00962781710318هاده رقم تلفون إذا حب يتبرع في جهاز يارب الله يبعت الخير