Microsoft Office Forums - View Single Post - [Solved] Compare sizes of different boxes and pick the correct size to put another box in to!

ljtjr · 01-12-2018, 09:56 PM

I am dazzled by NBVC's facility with the INDEX and MATCH functions. As a novice I find them confusing. He or she provided a really good formula (algorithm) for your box problem. I tried out both his formulas and they worked well, especially the second one that better optimizes by adding the volume criterion. I do have one suggestion that will optimize the result somewhat better. It uses NBVC's solution and the fact that orientation of the products in the boxes is arbitrary, which it seems NBVC has not invoked.

Rather than testing box width versus product width, height versus height, etc., it works better if for each box and product the test is short length versus short length, long length versus long length, and mid length versus mid length. Just rearrange the dimension arrays so that the short, mid, and long dimensions are in different columns and then apply NBVC's formula as before. The attached spreadsheet should make clear what I'm stumbling to explain.

The four solutions on the spreadsheet are to examine the consequences of changing the array orders as combinations of order reversals. I used your original data (yellow highlight) and added 2 box sizes to avoid fit fails (#N/A). The rearranged dimensions (min, mid, max) and the corresponding results are shaded blue. For this small test case, the best result was with the min-max,v algorithm (alg4) for which the average box filling was about 82%, thus allowing smaller boxes on average and the least packing filler.

Note that if both arrays are in ascending order by volume, NBVC's two algorithms (with and without the volume criterion) give the same results. Otherwise, alg1 did its best (55% avg fill) with box volumes in ascending order and product volumes in descending order. Worst of all is with the box volumes in descending order because all product sizes are matched to the largest box as it's encountered by the algorithm first.

I pass this on to you on the chance you may not have worked it out yourself. I have no interest in putting things in boxes, but I got into this to learn how experts use the INDEX and MATCH functions.