[Solved] Calculating the best method of packaging

[westclean]

I don't particularly want to create a function for this calculation if there is a clever way to do it with existing functions.

So here is an example problem: I want to enter the number of products purchased and then calculate the best way to ship based on my box sizes. The idea is to minimize boxes. I have 5 different sizes of boxes. The sizes will hold 1, 3, 10, 30, and 50 of the products. So if I entered an order of 90 units it would calculate that I needed two 50 unit boxes. If I entered an order of 79 it would calculate that I need one 50 unit box and one 30 unit box. This can use multiple cells, for example the 80 unit purchase:

Box Product
Box50 50 units
Box30 29 units

I hope this make sense to everyone, and hope you can help.

Thanks

[Villeroy]

[Solved] Can't figure out how to break $134 into...
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[westclean]

It seemed that this would be the best way to go until I started using it a bit. If I have a 50, 30, 10, 3, and 1 size box and my test value is 90, it returns that I should ship in one 50, one 30, and one 10 size box. This makes sense if I want a perfect fit, but I want to minimize shipping costs. Shipping 90 units in three boxes has the same shipping weight, but since it is in 3 boxes instead of 2 the price will be higher to ship. The most economical way would be to split the 90 units into two 50 boxes with 45 units.

We never ship over 300 units to a single customer, if that makes it easier.

[floris_v]

You need to play a bit with the numbers here, in any case it can pay to have some boxes not quite full, so add a little to the number of products in your calculations.
If the number of products is in A1, use =QUOTIENT(A1+10; 50) for the number of box50. I think that +10 is a good guess for optimality.
For the box30 you can use =IF(A10>0;QUOTIENT(A10+5;30);0) where A10=A1-50*A2
Note that the +5 is arbitrary, I didn't test for optimization. For the other boxes you can continue in that line.
However, I'm not a Calc expert, so it's possible that there's a more efficient way to do the calculations.
_________________
LibreOffice 3.6.3; OOo 3.4.1 on Windows Vista
Join the Official community forum - in several languages, including Nederlandstalig forum

[range]

You could use the solver, eg TOOLS>SOLVER.
The function to minimise would be something like:-

(total no of boxes)*1000000+(total capacity)
(so that it minimises the no of boxes, and then the total capacity)

put the condition (total capacity)>=(required capacity)
and specify the variables (number of each box size) as positive integers in solver options.

[Villeroy]

Units in B1:G1: 50, 30, 10, 3, 1, 0 [including a zero as last unit]
A2: test value
B2: =IF($A$2>$B$1;$B$1;INDEX($B$1:$G$1;MATCH($A$2;$B$1:$G$1;-1)))
C2: =IF($A2-SUM($B2:B2)>$B$1;$B$1;INDEX($B$1:$G$1;MATCH($A2-SUM($B2:B2);$B$1:$G$1;-1)))
copy C2 to the right
For A2=102 this returns 50, 50, 3, 0, 0, ... (2x50 and 1x3)

MATCH(val;scale;-1) utilizes the "descending ordered lookup" which matches at the last position where the search value is bigger than or equal to the scale value.

I feel there must be a more elegant solution.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[westclean]

Thanks, it works but like you said, it seems a bit messy. I should be able to work with this.

Thanks

[scsisys]

Have uploaded a sample file which you may want to use. The link is to 4Shared.com.

http://www.4shared.com/file/12ifUZhN/Packaging_calculator.html

scsisys
_________________
OO 3.2.1
Win XP /SP3