A simple excel formula problem that requires support from experts!

[Mr Kaka]

Hi,

I need your help in creating an excel formula that solve the following problem:

I need a formula that allows me to find the size of the smallest box that can contain a certain number of products.

On C10, I input the number of units, whereas on C13:C16 is where I would like to get the output.
On C6:C8 I reported the size of the product.
On I4:L15, I reported the available boxes.

Here you can see my worksheet:

Book2
	A	B	C	D	E	F	G	H	I	J
1
2
3		Product Dimension					Type of boxes
4		Longest side (cm)	20			ID	Longest side (cm)	Median side (cm)	Shortest side (cm)
5		Median side (cm)	8			1	20	20	10
6		Shortest side (cm)	6			2	20	20	20
7						3	30	20	20
8		Units				4	30	30	20
9						5	35	30	15
10		Box				6	35	29	19
11		ID				7	40	30	30
12		Longest side (cm)				8	43	35	31
13		Median side (cm)				9	50	37	32
14		Shortest side (cm)				10	50	40	40
15						11	60	40	40
16
Sheet1

Cell Formulas
Range	Formula
F6:F15	F6	=F5+1

 

[DanteAmor]

I adjusted the data in the sheet according to your description.

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L
1
2										Type of boxes
3									ID	Longest side (cm)	Median side (cm)	Shortest side (cm)
4									1	20	20	10
5		Product Dimension							2	20	20	20
6		Longest side (cm)	20						3	30	20	20
7		Median side (cm)	8						4	30	30	20
8		Shortest side (cm)	6						5	35	30	15
9									6	35	29	19
10		Units							7	40	30	30
11									8	43	35	31
12		Box							9	50	37	32
13		ID							10	50	40	40
14		Longest side (cm)							11	60	40	40
15		Median side (cm)
16		Shortest side (cm)
Hoja3

So in cell C13 the following formula:

=MINIFS(I4:I14,J4:J14,">="&C6,K4:K14,">="&C7,L4:L14,">="&C8)

In cell C14:

=TRANSPOSE(FILTER(J4:L14,I4:I14=C13))

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Mr Kaka]

I adjusted the data in the sheet according to your description.

So in cell C13 the following formula:

=MINIFS(I4:I14,J4:J14,">="&C6,K4:K14,">="&C7,L4:L14,">="&C8)

In cell C14:

=TRANSPOSE(FILTER(J4:L14,I4:I14=C13))

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Thanks Dante for the prompt reply.
Unfortunately your answer does not solve the problem.

More specifically the inputs of the problem are:

1. Product dimension: these dimensions represent the size of my product. Please bear in mind that the product can be placed in the box in any position, meaning that there is not a straight relation between (for example) the longest dimension of product with the longest dimension of the box, etc..
2. Number of products: if i have for example 5 Units of the product (Cell C10), I need a bigger box.

 

[DanteAmor]

1. Product dimension: these dimensions represent the size of my product. Please bear in mind that the product can be placed in the box in any position...
2. Number of products: if i have for example 5 Units of the product (Cell C10), I need a bigger box.

In my case, we need some auxiliary cells, let's say cells N4 to Q9.

In cell N4 put the following formula:

=LET(a,$C$6:$C$8*C10,VSTACK(INDEX(a,{1,2,3},0),ÍNDICE(a,{1,3,2},0),INDEX(a,{2,1,3},0),INDEX(a,{2,3,1},0),INDEX(a,{3,1,2},0),INDEX(a,{3,2,1},0)))

In cell Q4 this formula and copy down to Q9:

=MINIFS($I$4:$I$14,$J$4:$J$14,">="&N4,$K$4:$K$14,">="&O4,$L$4:$L$14,">="&P4)

In cell C13:

=MIN(Q4:Q9)

In cell C14:

=TRANSPOSE(FILTER(J4:L14,I4:I14=C13))

Example:

I hope it helps you.

 

[DanteAmor]

Ok, here's a single formula without the helper cells.

In cell C13:

=MIN(LET(m,LET(a,$C$6:$C$8*C10,VSTACK(INDEX(a,{1,2,3},0),INDEX(a,{1,3,2},0),INDEX(a,{2,1,3},0),INDEX(a,{2,3,1},0),INDEX(a,{3,1,2},0),INDEX(a,{3,2,1},0))),MINIFS(I4:I14,J4:J14,">="&INDEX(m,SEQUENCE(6),1),K4:K14,">="&INDEX(m,SEQUENCE(6),2),L4:L14,">="&INDEX(m,SEQUENCE(6),3))))

In Cell C14:

=TRANSPOSE(FILTER(J4:L14,I4:I14=C13))

Example (Just to demonstrate that it works with a sigle formula):

 

[Mr Kaka]

In my case, we need some auxiliary cells, let's say cells N4 to Q9.

In cell N4 put the following formula:

=LET(a,$C$6:$C$8*C10,VSTACK(INDEX(a,{1,2,3},0),ÍNDICE(a,{1,3,2},0),INDEX(a,{2,1,3},0),INDEX(a,{2,3,1},0),INDEX(a,{3,1,2},0),INDEX(a,{3,2,1},0)))

In cell Q4 this formula and copy down to Q9:

=MINIFS($I$4:$I$14,$J$4:$J$14,">="&N4,$K$4:$K$14,">="&O4,$L$4:$L$14,">="&P4)

In cell C13:

=MIN(Q4:Q9)

In cell C14:

=TRANSPOSE(FILTER(J4:L14,I4:I14=C13))

Example:
View attachment 94794


I hope it helps you.

Thanks Dante, I really appreciate your support on this problem.

I think there is an issue on the name_value of the LET formula. Indeed, if we set "$C$6:$C$8*C10" we are saying that there are "n^3" units and not "n" units.

As per your example, if we have 7 units of a product with dimension 4x5x2, we should use the box with dimension "20x20x10", given that in this box we can fit 100 units.
From your formula, I believe that we are getting the box "35x30x15" because you are asking to find a box that can contain 343 units (i.e. 7^3).

 

[DanteAmor]

Forget my formula, it's only to solve a part of the big problem. The first thing is to accommodate the packages in different ways, and each way look for the best option. then you have to find an algorithm that will accommodate the packets and form one big packet. I'm afraid that would be very complicated with a single formula. I guess it could be with vba.

 

[Mr Kaka]

thanks dante.
Do you think there can be a solution if we use more than a single formula? If yes, what it could be?

 

[DanteAmor]

Do you think there can be a solution if we use more than a single formula? If yes, what it could be?

Sincerely I dont know.
The formula system or the VBA must have the ability or skill to perform all possible combinations.

I tried to make an example of how to accommodate 6 packages. I got 9 different ways considering the width as the base, you have to rotate the packages to consider the height as the base, you have to rotate the packages to consider the length as the base.
Which will give us 27 combinations:

Then you will have to combine the dimension as the base, for example 2 packages with the width as the base, 2 packages with the height as the base and 2 packages with the length as the base, something like this:

Considering different bases, I don't know how many combinations could be given.

After obtaining more than 30 combinations, and of course, obtain the dimensions of length, width and height of each combination, each combination must be verified with the 11 box sizes, that is, make 330 comparisons and identify which is the most optimal.

I guess you already knew. I just wanted to carry out the exercise to gauge the size of the problem.

 

[Stiped]

Test PN's to Convert to New Boxes.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	ItemCode	ItemShortDesc	UnitCode	Produced	Sold	LabelWidth	LabelHeight	RollDiameter(inch)(W,D)	Roll Length	Roll Width	RollHeight (inch)	QtyPerBox	LayersPer Box	BoxesPerPallet	Core	Current Box#	Box Volume	Current Head Height	Product Volume	x	y	z	NewBox
2	LAKEY10038650	L 6.35mm x 6.35mm, 10035964RM	RL	True	True	0.25	0.25	6.90	6.90	6.90	0.4531	11.00		100.00	B300045-25	BOX103	465.1172	NA	237.293
3	LAKEY10038649	L 0.1563 X 0.1563, 10035964RM	RL	True	True	0.16	0.16	6.90	6.90	6.90	0.4531	16.00		84.00	B300045-25	BOX101	837.2109	NA	345.1535
4	LAKEY10018163	L 0.1563 X 0.1563, 68165RM	RL	True	True	0.16	0.16	6.90	6.90	6.90	0.4531	16.00		100.00	B300045-25	BOX101	837.2109	NA	345.1535
5	BOX290316-002	L 0.500 X 5.500, 68148RM	RL	True	True	0.50	5.50	4.00	4.00	4.00	0.5	36.00		96.00	Z100500	BOX114	461.3203	NA	288
6	10036113	L 0.250 X 0.250, 05827RM	RL	True	True	0.25	0.25	7.90	7.90	7.90	0.5	8.00		200.00	Z300500	BOX209	272	NA	249.64
7	10037132	L 0.250 X 0.250, 10026487RM	RL	True	True	0.25	0.25	7.90	7.90	7.90	0.5	8.00		200.00	Z300500	BOX209	272	NA	249.64
8	10030319	L 0.375 x 0.500, 66447RM	RL	True	True	0.38	2.00	7.90	7.90	7.90	0.5	10.00		100.00	Z300500	BOX103	465.1172	NA	312.05
9	10023519	L 0.375 X 0.375, 66680RM	RL	True	True	0.38	0.38	7.90	7.90	7.90	0.5	15.00		100.00	Z300500	BOX103	465.1172	NA	468.075
10	LAKEY10020816	L 0.3906 X 0.3906, 68165RM	RL	True	True	0.39	0.39	7.70	7.70	7.70	0.515	18.00		84.00	P300515	BOX119	660.1563	NA	549.6183
Rolls

Cell Formulas
Range	Formula
I2:I10	I2	=H2
J2:J10	J2	=H2
K2:K10	K2	=IFNA(VLOOKUP(O2,'CoreSizes'!$A$1:$B$1499,2,FALSE),NA)
Q2:Q10	Q2	=VLOOKUP(P2,'https://zebra-my.sharepoint.com/personal/dstone_zebra_com/Documents/Documents/!!Box Optimization Project/[ItemListRequest_Final_Live.xlsx]FullBoxList'!$A$1:$F$713022,6,FALSE)
S2:S10	S2	=PRODUCT(I2,J2,K2,L2)

Test PN's to Convert to New Boxes.xlsx
	A	B	C	D	E	F	G	H
1	BoxID	Length	Width2	Height	Volume	Min	Mid	Max
2	BOX116	8.125	6.125	4.25	211.5039063	4.25	6.125	8.125
3	BOX133	13.25	6.25	4.25	351.953125	4.25	6.25	13.25
4	BOX138	10.25	7.5	4.75	365.15625	4.75	7.5	10.25
5	BOX175	11.25	8.125	5	457.03125	5	8.125	11.25
6	BOX114	10.125	10.125	4.5	461.3203125	4.5	10.125	10.125
7	BOX103	7.875	7.875	7.5	465.1171875	7.5	7.875	7.875
8	BOX195	12.5	8.5	5.5	584.375	5.5	8.5	12.5
9	BOX134	13.25	8.25	5.75	628.546875	5.75	8.25	13.25
10	BOX115	15	10	5	750	5	10	15
11	BOX176	11.25	8.125	8.5	776.953125	8.125	8.125	11.25
12	BOX101	15.75	7.875	6.75	837.2109375	6.75	7.875	15.75
13	BOX111	15.75	7.875	8.5	1054.265625	7.875	7.875	15.75
14	BOX104	15	10	8.5	1275	8.5	10	15
15	BOX102	15.75	7.875	10.75	1333.335938	7.875	7.875	15.75
StockBoxList

Cell Formulas
Range	Formula
E2:E15	E2	=PRODUCT(StockBoxList[@[Length]:[Height]])
F2:F15	F2	=MIN(StockBoxList[@[Length]:[Height]])
G2:G15	G2	=SUM(StockBoxList[@[Length]:[Height]])-[@Length]-[@Height]
H2:H15	H2	=MAX(StockBoxList[@[Length]:[Height]])