[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
westclean
General User

Joined: 18 Apr 2012
Posts: 6
Location: United States

 Posted: Tue Apr 24, 2012 12:56 pm    Post subject: [Solved] Calculating the best method of packaging 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. ThanksLast edited by westclean on Wed Apr 25, 2012 6:39 am; edited 3 times in total
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

 Posted: Wed Apr 25, 2012 2:21 am    Post subject: [Solved] Can't figure out how to break \$134 into..._________________Rest in peace, oooforum.org Get help on http://forum.openoffice.org
westclean
General User

Joined: 18 Apr 2012
Posts: 6
Location: United States

 Posted: Wed Apr 25, 2012 3:55 am    Post subject: Works for money, but not quite for this 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
Moderator

Joined: 12 Jul 2007
Posts: 4603
Location: Netherlands

 Posted: Wed Apr 25, 2012 4:22 am    Post subject: 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
General User

Joined: 04 Jan 2012
Posts: 21

 Posted: Wed Apr 25, 2012 5:25 am    Post subject: 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
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

 Posted: Wed Apr 25, 2012 5:35 am    Post subject: 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
General User

Joined: 18 Apr 2012
Posts: 6
Location: United States

 Posted: Wed Apr 25, 2012 6:38 am    Post subject: Works Thanks, it works but like you said, it seems a bit messy. I should be able to work with this. Thanks
scsisys
OOo Enthusiast

Joined: 17 Dec 2009
Posts: 164

 Posted: Wed Apr 25, 2012 4:41 pm    Post subject: 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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum