PROBLEM - how many cartons fit in that box

[Tradesman]

I've been trying to work a formula but looking online I think it may be a bit more complex!

I have a carton a particular size eg 9cm x 9cm x 22cm- I want to know how many go into a space 60cm x 40cm x 30cm.

there seems to many pallet solutions in software- but not for cartons into a larger space- the fit can leave space but rather than sitting drawing it out- which is what Im doing! is there a solution that can work this out- then present the solution somehow???

just a thought may be a macro solution!!

[mrice]

Try this user defined function

Please Login or Register  to view this content.

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

A1 = 9, B1 = 9, C1 = 22 D1 = 60 E1 = 40 F1 = 30


In G1, enter = Fit(A1,B1,C1,D1,E1,F1)

Remember to save the workbook as a macro enabled workbook .xlsm

I've assumed that the orientation of the cartons in the box is not important.

[Tradesman]

this works foer the same layers but take the shape 10cm x 9cm x 22.5

logic suggests 4 x 10cm on the 40cm side - 2 x 22.5cm on the 60cm side-leaving ( 60cm - 45cm =15cm)

three layer so of 9cm = 24

the formula doesnt take into account toe 15cm wide by 40cm x 30cm gap at one end - where I get 3 more of the size fitting in.....

not sure this is doable in excel - what do you think

[mrice]

Probably needs a recursive solution - will think about it.

[mrice]

Try this

Please Login or Register  to view this content.

The function predicts that you can get 30 cartons into the box (I would need a box and a set of cartons to convince myself). It works by finding the maximum number in a single cuboid and then repeats the exercise to find the maximum for the cuboid portions of the space that is left.

With the largest face as the horizontal base, have a 6 layers of 3 cartons with 60 aligned with 9, and 22 aligned with 40. This leaves a cuboid space of width 60, depth 18 and height 30. This is filled with a single layer of 12 cartons with the square face horizontal.

[Tradesman]

sorry now just get a #value error

[mrice]

Sorry - function now needs an extra parameter

In G1, enter = Fit(A1,B1,C1,D1,E1,F1,0)

[Tradesman]

Im going barmey- that works great except when i re-open the macro enabled workbook I get the #value error in G1 ( the fit function is in the module ) - or if I over type a value in A1 to F1????

[mrice]

Are macros enabled when you open it?

[Tradesman]

Hi all figured cheers

me being a numpty