Cube root help (excel/VBA)

[jjohall2000]

Hi,

I am making a program in Excel to find the optimum box size. i know to get the box size you need to ^volume 1/3 and then you round it up, but this dose not give the optimum box size all the time as it would need to be a multiple of 5 or 10. So this means i need to work out the length, width and height. but i cannot find the formula. Please can some help me?

Many Thanks

jjohall2000

[zaza]

Box volume = length * width * height.


??

Can you please explain what you are on about?



zaza

[jjohall2000]

Box volume = length * width * height.


??

Can you please explain what you are on about?



zaza

Hi,

Its quite hard to explain but i will explain it again,

I am using Excel with a bit of VBA to create a spreadsheet which works out the best box size (the length, width and height) i know one way of doing this but it is not the best box size ( There would be space left over) the way is ^volume 1/3 and then round the number up. There is a better way of doing it which would leave no space left over. this is what i need help with.

Many Thanks

jjohall2000

[zaza]

Perhaps this is an easier way to explain it: You are given a volume and you have to work out the integer dimensions of a box which has a volume that is as close as possible to the given.


Is this what you are, in fact, trying to ask? Do the sides have to be multiples of 5 or 10?

[jjohall2000]

Perhaps this is an easier way to explain it: You are given a volume and you have to work out the integer dimensions of a box which has a volume that is as close as possible to the given.


Is this what you are, in fact, trying to ask? Do the sides have to be multiples of 5 or 10?

zaza, you have "hit the nail on the head" (as the saying goes), That is exactly what i want to do. in answer to your question the sides do not have to multiples of 5 or 10, that is just what i have been told by other people, but i need to find the best Length, width and height, for a given volume.

[zaza]

If your volume is V, then the sides you want are:


Width: 1
Height: 1
Length: V



...or are there other conditions?

[Milk]

Width: 1
Height: 1
Length: V

Lol, zaza has a very good point, given the conditions you have stated that is the perfect solution. Are the boxes supposed to hold items of any particular dimensions? Do you want the boxes to be as near to a cube as possible?

[jjohall2000]

Lol, zaza has a very good point, given the conditions you have stated that is the perfect solution. Are the boxes supposed to hold items of any particular dimensions? Do you want the boxes to be as near to a cube as possible?

Yeah the box will hold different size cubes, i have worked out the volume of the box need i just need to find the best height, length and width

[zaza]

I think you are not telling us the full question.

[Milk]

The best height, length and width of you box is dictated by the height, length and width of the cubes you want to hold. Without knowing what those dimensions are... 1x1xVolume is the best solution. In a way the volume is irrelevant, it's certainly secondary to the dimensions of the differently sized cubes.

[jemidiah]

I know this is a few weeks old but...

Perhaps by "best" you mean "least surface area"? In such a case you would want to use length = width = height = volume^(1/3) like you were suggesting, so it makes sense that that's what you're after.

Of course volume^(1/3) with any final desired volume would be wrong if the volume wasn't a perfect cube, and you're only trying to fit small boxes inside this bigger box--you would usually not be able to fit in the outer layer of small boxes without smashing them, if the length, width, and height was exactly V^1/3. As you suggested, you could round the result up, but that could result in a lot of extra space used.

Now... I might be able to pose this question algebraically, but I smell a diophantine equation (ick).

Find values of L, H, and W such that LHW >= V (volume), 2LH + 2HW + 2LW (surface area) is minimized [L, H, and W are positive integers]. This needs to be refined (otherwise the solution is L=H=W=V^1/3, which you clearly don't like)--how bad is an extra space in the box, and how expensive is the surface area?

You'd end up defining a cost function C(L, H, W) that you would want to minimize using integers for L, H, and W (since otherwise you'd have to smash small boxes into the larger box to fill up the space, or you would be making a side pointlessly large): I would use something like C(L, H, W) = a(V - LHW) + b(2LH + 2HW + 2LW) for some weights a and b, for only integers L, H, and W.

What this is saying algebraically is "The cost of making a box of length L, height H, and width W, is the number of extra spaces in the box * a [which would be a cost per storage space] added to the surface area * b [which would be the cost per unit area of the box's surface]."

So again, this becomes:
Minimize C where:
C(L, H, W) = a(V - LHW) + b(2LH + 2HW + 2LW)
a, b, V, L, H, W are non-negative integers [ >=0 ]

Finally, you could probably solve this inequality by using some mix of linear programming and diophantine analysis, though I'm not really familiar with either so I couldn't be sure. Linear programming would give you non-integer solutions, and diophantine analysis (to my limited knowledge) doesn't deal with inequalities, but perhaps combined they could solve it.

So... yeah... the problem that I *think* you're posing is in fact really tough to answer in a general sense, and you would probably be better to do one of these two things:

(i) Brute force: just try every combination of L, H, and W, and see which one you like the best. With modern computers, you could very easily check boxes up to size ~1000 [1 billion combinations, with a volume that is coincidentally 1 billion units] within a few seconds, and just pick the one that you like the best.

Edit: These estimates suck. New estimate: first pick a value from 1...V for L; now pick a value from 1...V/L for W; and finally H = V/(LW) so that LWH = V. This gives V initial choices, and an average of the Vth harmonic number (~7.5 for V = 1000; ~14.4 for V = 1000000; it grows very slowly) of choices after that to uniquely determine L, W, and H [this assumes permutations of a given LWH shouldn't be counted; if they were... then your box apparently isn't a box...], which gives V * H_V possible box combinations [H_V is the Vth harmonic number, which, sadly, grows without bound and V->infinity]. That is, you would have to check less than 15*V different configurations for V between 1 and 1,000,000, which is very, very easy on a computer. 1 billion combos might take an hour or so, but I doubt you'll have a box that holds 1 billion small boxes, and even if you did, you would only have to do this computation one time.

(ii) Just pick an algorithm after you've tried a few out. You could go with your "round up" method, you could use the L=W=1, H=V that's been suggested, you could go with L=2, W=3, H = V/6 for no apparent reason, etc. There are a huge number of algorithms you could use. I like brute force much, much better.