Forum Discussion

sg2021's avatar
sg2021
Copper Contributor
Feb 12, 2021

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 ...
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Feb 13, 2021

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.

  • mtarler's avatar
    mtarler
    Silver Contributor
    Feb 15, 2021

    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))
    • sg2021's avatar
      sg2021
      Copper Contributor
      Feb 15, 2021

      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

       

      • mtarler's avatar
        mtarler
        Silver Contributor
        Feb 15, 2021

        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.

    • sg2021's avatar
      sg2021
      Copper Contributor
      Feb 15, 2021

      mtarler 

       

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

  • sg2021's avatar
    sg2021
    Copper Contributor
    Feb 15, 2021
    Thank you for your response! I will give this a shot and update if I cannot make it a go. Thanks!

Resources