Dec 31 2019 04:52 AM
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 how I intended. Any help would be greatly appreciated. Bonus points for also finding the next size material based off the face material.
Dec 31 2019 09:47 AM
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.
Dec 31 2019 10:11 AM
Thank you! @Sergei Baklan
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.
Jan 01 2020 08:23 AM
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))
Jan 02 2020 04:51 AM
Jan 02 2020 04:53 AM
@j-bird , you are welcome
Jan 02 2020 08:42 AM
This is only available from the most up to date versions of Office 365. The new lookup function
= XLOOKUP( W*L, IF((width>=W)*(length>=L), width*length ), dimensions, , 1 )
is capable of identifying the next largest value (I have used an area) and returning both dimensions in a single lookup.
Jan 02 2020 11:33 AM - edited Jan 04 2020 10:25 PM
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".
Jan 02 2020 12:20 PM
I guess you are right, I took difference of perimeters, in your case that's difference of squares (same as @Peter Bartholomew 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))