Feb 12 2021 01:47 PM
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!
Feb 12 2021 06:30 PM
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.
Feb 12 2021 11:19 PM
@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.
Feb 15 2021 08:15 AM
Good morning,
Thank you for taking the time to reply to my request. Below are more for you to review. Thanks for your time!
Feb 15 2021 08:16 AM
Feb 15 2021 08:19 AM
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.
Feb 15 2021 10:43 AM
@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))
Feb 15 2021 10:52 AM
Perfect. Thank you for the addition. I will give this a shot and update if something goes amidst. Thanks!
Feb 15 2021 11:38 AM
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
Feb 15 2021 11:43 AM - edited Feb 15 2021 11:49 AM
@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.
Feb 15 2021 01:10 PM
Feb 15 2021 02:43 PM
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?
Feb 15 2021 03:43 PM
Feb 15 2021 06:31 PM
@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.
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.