Calculate how many products will fit in the carton | Google Sheet

Question

at the beginning I inform you that I didn't find an answer on stackoverflow for this topic.

I am looking for a function in Google sheet that would be able to automatically calculate for me how many packages of matches will fit in a collective carton

I currently have this data:

product dimensions

| product    | length | width | height
| -----------|----------------|-------
| product 1  | 736    | 82    | 44

the dimensions of the collective carton

| carton      | length | width | height
| ------------|----------------|-------
| Big Shelves | 1050   | 390   | 600
| Mezzanine   | 600    | 270   | 260

I tried converting by cubic meters but it doesn't count correctly for example it calculates as follows

'Product 1' in 'Big Shelves' fits 93 pcs. And I am aware that there will be less products
'Product 1' in 'Mezzanine' fits 16 pcs. Where it is not possible because the product is longer than the longest side of the collective carton

I created a spreadsheet in google for easier viewing:
https://docs.google.com/spreadsheets/d/1dRaMFl3j-tUaKWljR9ntUpSV7jh5g1-FSw4FGGeT9d8/edit?usp=sharing

Name product	Battery		Kg	length	width	height	Vol, m3
			0,25	0.736	0.082	0.044	0.002655488
Pallet capacity		KG		Length	Width	Height	Vol, m3
Mezzanine -	16	4	Big Shelves	1.05	0.39	0.6	0.2457
Big Shelves	93	23.25	Mezzanine	0.6	0.27	0.26	0.04212

I am waiting for suggestions and answers.

Answer 1

I found a solution to my problem, I'm pasting the formula for others, maybe it will help someone like me

the first formula that calculates the longest side

=(ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3)))*(ROUNDDOWN(MIN(G6:I6)/MIN(G3:I3)))*(ROUNDDOWN(SMALL(G6:I6,2)/SMALL(G3:I3,2)))

and the second formula that calculates the shortest side

=(ROUNDDOWN(MAX(G6:I6)/SMALL(G3:I3;2)))*(ROUNDDOWN(SMALL(G3:I3;2)/MAX(G6:I6)))*(ROUNDDOWN(SMALL(G6:I6;2)/SMALL(G3:I3;2)))

Note to subsequent readers:

The above is not an optimal strategy because it fixes the orientation of items in the container by a rigid rule and does not allow for other orientations. In the general case, it will not find the true number of items that will fit in a container. Here is a simple example of why that matters:

See packing_problem, rectangle packing and bin packing problem for more information.

The following solution attempt will give the correct number of boxes in some scenarios, but will give an incorrect result in others:

first formula

=(ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3)))*(ROUNDDOWN(MIN(G6:I6)/MIN(G3:I3)))*(ROUNDDOWN(SMALL(G6:I6,2)/SMALL(G3:I3,2)))+((MAX(G6:I6)-ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3))*MAX(G3:I3)))/SMALL(G3:I3,2)

second formula

=((ROUNDDOWN(MAX(G6:I6)/SMALL(G3:I3;2))*ROUNDDOWN(SMALL(G6:I6;2)/MAX(G3:I3)))*ROUNDDOWN(MIN(G6:I6)/SMALL(G3:I3;2)))+ROUNDDOWN((SMALL(G6:I6;2)-MAX(G3:I3))/SMALL(G3:I3;2))*ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3))

third formula that combines the above formulas, choosing the larger of their results:

=IF((ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3)))*(ROUNDDOWN(MIN(G6:I6)/MIN(G3:I3)))*(ROUNDDOWN(SMALL(G6:I6;2)/SMALL(G3:I3;2)))+((MAX(G6:I6)-ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3))*MAX(G3:I3)))/SMALL(G3:I3;2)>((ROUNDDOWN(MAX(G6:I6)/SMALL(G3:I3;2))*ROUNDDOWN(SMALL(G6:I6;2)/MAX(G3:I3)))*ROUNDDOWN(MIN(G6:I6)/SMALL(G3:I3;2)))+ROUNDDOWN((SMALL(G6:I6;2)-MAX(G3:I3))/SMALL(G3:I3;2))*ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3));(ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3)))*(ROUNDDOWN(MIN(G6:I6)/MIN(G3:I3)))*(ROUNDDOWN(SMALL(G6:I6;2)/SMALL(G3:I3;2)))+((MAX(G6:I6)-ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3))*MAX(G3:I3)))/SMALL(G3:I3;2);((ROUNDDOWN(MAX(G6:I6)/SMALL(G3:I3;2))*ROUNDDOWN(SMALL(G6:I6;2)/MAX(G3:I3)))*ROUNDDOWN(MIN(G6:I6)/SMALL(G3:I3;2)))+ROUNDDOWN((SMALL(G6:I6;2)-MAX(G3:I3))/SMALL(G3:I3;2))*ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3)))

Note: the second and third formulas above will happily pack six 1x1x1 cubes in a 2x2x1 box, which is incorrect. Here is an illustration why finding the optimal packing strategy is such a hard problem:

The third formula can be rewritten as a lambda to make it a tad easier to follow (but it will still give incorrect results):

=lambda(
  itemX, itemY, itemZ,
  boxX, boxY, boxZ,
  lambda(
    numFitXX, numFitYY, numFitZZ,
    numFitXY, numFitYX, numFitZY,
    max(
      numFitXX * numFitYY * numFitZZ + (boxX - itemX * numFitXX) / itemY,
      numFitXY * numFitYX * numFitZY + numFitXX * rounddown((boxY - itemX) / itemY)
    )
  )(
    rounddown(boxX / itemX), rounddown(boxY / itemY), rounddown(boxZ / itemZ),
    rounddown(boxX / itemY), rounddown(boxY / itemX), rounddown(boxZ / itemY)
  )
)(
  max(G3: I3), small(G3: I3, 2), min(G3: I3),
  max(G6: I6), small(G6: I6, 2), min(G6: I6)
)

Sorry, my fault, I pasted the wrong code :) it is corrected

The third code that selects a larger quantity

=IF((ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3)))*(ROUNDDOWN(MIN(G6:I6)/MIN(G3:I3)))*(ROUNDDOWN(SMALL(G6:I6;2)/SMALL(G3:I3;2)))+ROUNDDOWN(MAX(G6:I6)-(ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3))*MAX(G3:I3)))*ROUNDDOWN(SMALL(G6:I6;2)/MAX(G3:I3))>((ROUNDDOWN(MAX(G6:I6)/SMALL(G3:I3;2))*ROUNDDOWN(SMALL(G6:I6;2)/MAX(G3:I3)))*ROUNDDOWN(MIN(G6:I6)/SMALL(G3:I3;2)))+ROUNDDOWN(SMALL(G6:I6;2)-(ROUNDDOWN(SMALL(G6:I6;2)/MAX(G3:I3))*MAX(G3:I3)))*ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3));(ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3)))*(ROUNDDOWN(MIN(G6:I6)/MIN(G3:I3)))*(ROUNDDOWN(SMALL(G6:I6;2)/SMALL(G3:I3;2)))+ROUNDDOWN(MAX(G6:I6)-(ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3))*MAX(G3:I3)))*ROUNDDOWN(SMALL(G6:I6;2)/MAX(G3:I3));((ROUNDDOWN(MAX(G6:I6)/SMALL(G3:I3;2))*ROUNDDOWN(SMALL(G6:I6;2)/MAX(G3:I3)))*ROUNDDOWN(MIN(G6:I6)/SMALL(G3:I3;2)))+ROUNDDOWN(SMALL(G6:I6;2)-(ROUNDDOWN(SMALL(G6:I6;2)/MAX(G3:I3))*MAX(G3:I3)))*ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3)))

second code

=((ROUNDDOWN(MAX(G6:I6)/SMALL(G3:I3;2))*ROUNDDOWN(SMALL(G6:I6;2)/MAX(G3:I3)))*ROUNDDOWN(MIN(G6:I6)/SMALL(G3:I3;2)))+ROUNDDOWN(SMALL(G6:I6;2)-(ROUNDDOWN(SMALL(G6:I6;2)/MAX(G3:I3))*MAX(G3:I3)))*ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3))

first code

=(ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3)))*(ROUNDDOWN(MIN(G6:I6)/MIN(G3:I3)))*(ROUNDDOWN(SMALL(G6:I6;2)/SMALL(G3:I3;2)))+ROUNDDOWN(MAX(G6:I6)-(ROUNDDOWN(MAX(G6:I6)/MAX(G3:I3))*MAX(G3:I3)))*ROUNDDOWN(SMALL(G6:I6;2)/MAX(G3:I3))

and yes, you are right, this is a very big problem but for me this option is sufficient, it is possible that someone will be able to: D create a formula that will calculate it

Now this code should work properly

=IF((ROUNDDOWN(IF(MOD(MAX(G6:I6)/MAX($G$3:$I$3),1)=0.333333333333334,MAX(G6:I6)/MAX($G$3:$I$3),ROUNDDOWN(MAX(G6:I6)/MAX($G$3:$I$3)))*IF(MOD(SMALL(G6:I6,2)/MIN($G$3:$I$3),1)=0.333333333333334,SMALL(G6:I6,2)/MIN($G$3:$I$3),ROUNDDOWN(SMALL(G6:I6,2)/MIN($G$3:$I$3)))*   IF(MOD(MIN(G6:I6)/SMALL($G$3:$I$3,2),1)=0.333333333333334,MIN(G6:I6)/SMALL($G$3:$I$3,2),ROUNDDOWN(MIN(G6:I6)/SMALL($G$3:$I$3,2)))))>ROUNDDOWN(IF(MOD(MAX(G6:I6)/SMALL($G$3:$I$3,2),1)=0.333333333333334,MAX(G6:I6)/SMALL($G$3:$I$3,2),ROUNDDOWN(MAX(G6:I6)/SMALL($G$3:$I$3,2)))*IF(MOD(SMALL(G6:I6,2)/MAX($G$3:$I$3),1)=0.333333333333334,SMALL(G6:I6,2)/MAX($G$3:$I$3),ROUNDDOWN(SMALL(G6:I6,2)/MAX($G$3:$I$3)))*IF(MOD(MIN(G6:I6)/SMALL($G$3:$I$3,2),1)=0.333333333333334,MIN(G6:I6)/SMALL($G$3:$I$3,2),ROUNDDOWN(MIN(G6:I6)/SMALL($G$3:$I$3,2)))),ROUNDDOWN(IF(MOD(MAX(G6:I6)/MAX($G$3:$I$3),1)=0.333333333333334,MAX(G6:I6)/MAX($G$3:$I$3),ROUNDDOWN(MAX(G6:I6)/MAX($G$3:$I$3)))*IF(MOD(SMALL(G6:I6,2)/MIN($G$3:$I$3),1)=0.333333333333334,SMALL(G6:I6,2)/MIN($G$3:$I$3),ROUNDDOWN(SMALL(G6:I6,2)/MIN($G$3:$I$3)))*   IF(MOD(MIN(G6:I6)/SMALL($G$3:$I$3,2),1)=0.333333333333334,MIN(G6:I6)/SMALL($G$3:$I$3,2),ROUNDDOWN(MIN(G6:I6)/SMALL($G$3:$I$3,2)))),ROUNDDOWN(IF(MOD(MAX(G6:I6)/SMALL($G$3:$I$3,2),1)=0.333333333333334,MAX(G6:I6)/SMALL($G$3:$I$3,2),ROUNDDOWN(MAX(G6:I6)/SMALL($G$3:$I$3,2)))*IF(MOD(SMALL(G6:I6,2)/MAX($G$3:$I$3),1)=0.333333333333334,SMALL(G6:I6,2)/MAX($G$3:$I$3),ROUNDDOWN(SMALL(G6:I6,2)/MAX($G$3:$I$3)))*IF(MOD(MIN(G6:I6)/SMALL($G$3:$I$3,2),1)=0.333333333333334,MIN(G6:I6)/SMALL($G$3:$I$3,2),ROUNDDOWN(MIN(G6:I6)/SMALL($G$3:$I$3,2)))))