Formula assistance requested - Involves dimensions of items

[Springer_30]

Hi there

I have a problem whereby I need to ascertain whether a specific dimensioned item will fit into one of four fixed bin locations, and also determine which bin it fits best

For example an item has the following dims:

Length: 54
Width: 15
Height: 30

The four bin locations available are as follows:

Small	Medium	Large	X-Large
25​	41,5​	41,5​	120​
39​	39​	77​	77​
50​	50​	50​	50​

How do I determine which bin the item will fit into best? I have tried various IF statements but with no real luck, I think I am missing something somehow

I have approximately 52000 items needing placement, those that are deemed too large to fit any of the bins will be oversized item

 

[JimM]

Does it matter what orientation the item goes into the bin eg in your example would it go into the XL bin as that's the only one that is long enough or would it go into the Large bin as the width of the bin would accommodate the length of the item?

 

[Springer_30]

Hi JimM

Orientation does not matter, so in this example it would fit into the Large bin as the width can accommodate the item's length

 

[JimM]

OK, this isn't the prettiest but think it works

You'll need to adapt it to fit your data ranges but assuming the item sizes are in B1:B3 and the bin dimensions are in A9:D12 (A9 being the word small and D12 the number 50)

=IF(AND(LARGE(B1:B3,1)<=LARGE(A10:A12,1),LARGE(B1:B3,2)<=LARGE(A10:A12,2),LARGE(B1:B3,3)<=LARGE(A10:A12,3)),A9,IF(AND(LARGE(B1:B3,1)<=LARGE(B10:B12,1),LARGE(B1:B3,2)<=LARGE(B10:B12,2),LARGE(B1:B3,3)<=LARGE(B10:B12,3)),B9,IF(AND(LARGE(B1:B3,1)<=LARGE(C10:C12,1),LARGE(B1:B3,2)<=LARGE(C10:C12,2),LARGE(B1:B3,3)<=LARGE(C10:C12,3)),C9,IF(AND(LARGE(B1:B3,1)<=LARGE(D10:D12,1),LARGE(B1:B3,2)<=LARGE(D10:D12,2),LARGE(B1:B3,3)<=LARGE(D10:D12,3)),D9,"Oversize"))))

 

[Springer_30]

Thank you JimM!!!!

It works perfectly after I adapted it to fit my data