Forum Discussion
Finding the next largest material in both length and width
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".
I guess you are right, I took difference of perimeters, in your case that's difference of squares (same as PeterBartholomew1 took), that will be more correct. Anyway, any calculation could be used within formula in (...)=AGGREGATE part
=INDEX(Material,MATCH(1,INDEX( --((Width*Length-C2*C3)=AGGREGATE(15,6,1/(Width>=C2)/(Length>=C3)*(Width*Length-C2*C3),1)),0),0))