Forum Discussion

j-bird's avatar
j-bird
Copper Contributor
Dec 31, 2019

Finding the next largest material in both length and width

I am looking for a way to find the next size material based off any given width and length. I tried using index match to search for multiple criteria at the same time but could never get it to work h...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 31, 2019

j-bird 

It depends on logic for "next size". If min of Width-C2+Length-C3 that could be

=INDEX(Material,MATCH(1,INDEX( --(C2-Width+C3-Length=AGGREGATE(15,6,1/(Width<C2)/(Length<C3)*(C2-Width+C3-Length),1)),0),0))

didn't catch the second question.

  • j-bird's avatar
    j-bird
    Copper Contributor
    Dec 31, 2019

    Thank you! SergeiBaklan 

     

    This was great! Instead of finding the "next size" that is less than the given value though I need to return the value that is the "next size" greater than or equal to the given width and length.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 01, 2020

      j-bird 

      That will be practically the same, only change sign of criteria in AGGREGATE and in calculating the difference in sizes:

      =INDEX(Material,MATCH(1,INDEX( --((Width-C2+Length-C3)=AGGREGATE(15,6,1/(Width>=C2)/(Length>=C3)*(Width-C2+Length-C3),1)),0),0))

       

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Jan 02, 2020

        SergeiBaklan 

        I came up with the formula above (after you posted your first one) but hesitated to post it. The formula seeks the most efficient (i.e. minimum waste) for the desired dimensions, allowing for more than one piece to be produced out of the material. It further takes Length and Width as separate (non inter-changeable) parameters. In other words, a piece 10L X 5W will not be made out of a material 5L X 10W , e.g. due to the pattern or structure of the material. (formula edited 5 Jan, 2020):

         

         

        =INDEX(Material,IFERROR(MATCH(1,1/(MOD(Width,$C$2)=0)*1/(MOD(Length,$C$3)=0),0),MATCH(AGGREGATE(14,6,(1/(1/(Width>=$C$2)*(Length>=$C$3)*(MOD(Width,$C$2)*Length+MOD(Length,$C$3)*Width))),1),1/(MOD(Width,$C$2)*Length+MOD(Length,$C$3)*Width),0)),1)

         

         

        I find material code "2431316" which gives a waste of (304.8-300) X 908.1 + (908.1-875) X 304.8 = 14447.76

        Your formula identifies code "2220873" when I enter it into the schedule. Correct? That one gives me a total waste of (333.5-300) X 875 + (875-875) X 333.5 = 29312.5

         

        Up to j-bird to decide what in fact is the most desirable "next size".

         

         

         

Resources