algorithm creation

[Guest]

i have 2 size shipping containers that are available
the large can hold up to 36 bags of product and the small up to 6 bags
how would i go about creating an algorithm that would calculate how many
(the least number) small and large boxes i would need to use?

I've gotten this far:

Bags Small Large
1-6 1 0
7-12 2 0
13-18 3 0
19-24 0 1
25-30 0 1
31-36 0 1
37-42 1 1
43-48 2 1
49-54 3 1
55-60 0 2
61-66 0 2
67-72 0 2
73-78 1 2
79-84 2 2
85-90 3 2
91-96 0 3
97-102 0 3
103-108 0 3
109-114 1 3
115-120 2 3
121-126 3 3
127-132 0 4
133-138 0 4
139-144 0 4
etc etc

and now i'm a bit stuck - any help is greatly appreciated

thanks!

 

[Tim Williams]

Divide number of bags by 36 and round down = number of large containers
Divide remainder by 6 and round up if not exact = number of small containers

Sounds like a homework assignment.

Tim.

 

[Jim Cone]

Gixxer_J,

Do you actually mean the least number of boxes or
the least cost?
Using the large box would always give you the
least number of boxes.

Jim Cone
San Francisco, USA

 

[Guest]

no homework assignment - its part of the order entry system i'm working on to
determine packaging costs.

and this would be the least number of boxes - balanced with the lowest cost.

these containers will be used to ship frozen product overnight - so the
fewer the better and the most product packed in the least number of boxes is
my goal.

its been a while since i've been in class and my programming hasn't been
used in quite a long time.

thanks for the help!

J

 

[Guest]

wouldn't the remainder need to be multiplied by 36 and then divided by 6? (or
just multiplied by 6)?

 

[Guest]

i did forget one part to it - if there's more than 3 small boxes, they can be
consolidated into 1 large box

sorry to be a pain =)

 

[Jim Cone]

J,

You would probably want to figure in...

cost of each box
shipping costs
product loss/damage % during shipment per size
customer preference for a particular size
your internal handling/packing costs for a particular size

Regards,
Jim Cone

 

[Tim Williams]

I meant "the remaining bags" not the fractional remainder....

Tim.

 

[Guest]

this portion of my code is to figure out the # of boxes that will be used for
an order and thus calculate the cost of the boxes. shipping costs and
handling / packaging will depend on the # of boxes, product loss/damage is
negligable for this particular situation. customer preference is always the
cheapest possible, which is why i wanted to figure the least number of boxes.
the small box is half the cost of a large box, but the large box can contain
6 times the product as the small.

 

[Myrna Larson]

Divide number of bags by 36 and round down = number of large containers

Divide remainder by 6 and round up if not exact = number of small containers

That doesn't work quite right. Let's say the number of bags = 32. Your
algorithm gives 6 small bags:

32/36, rounded down = 0
32/6 = 5.333, rounded up = 6

The result should be 1 large bag, not 6 small ones.

The following takes care of the problem by first rounding the number of bags
UP to a multiple of 6.

To round UP, I take advantage of the fact that the INT function always rounds
DOWN. We can force rounding UP by double negation: Negate the number, take the
INT function, then negate the result. i.e.

If X > 0 and Y > 0, -INT(-X/Y) will round up.

Sub NumContainers(NumBags As Long, Big As Long, Small As Long)
Dim N As Long
N = -Int(-NumBags / 6) * 6 'round up to a multiple of 6
Big = N \ 36
Small = (N Mod 36) \ 6
End Sub

or, to make it a function, it has to return an array

Function NumContainers(NumBags As Long) As Variant
Dim N As Long
N = -Int(-NumBags / 6) * 6 'round up to a multiple of 6
NumContainers = Array(N \ 36, (N Mod 36) \ 6)
End Sub

 

[Myrna Larson]

Given that additional information:

Sub NumContainers(NumBags As Long, Big As Long, Small As Long)
Dim N As Long

N = -Int(-NumBags / 6) * 6 'round up to a multiple of 6
Big = N \ 36
Small = (N Mod 36) \ 6
If Small > 3 Then
Big = Big + 1
Small = 0
End If
End Sub

 

[Dana DeLouis]

Hello. If I understand the question correctly...
If A1 holds your number, then perhaps a helper column at B1 with
=MOD(A1,36)

The number of small boxes:
=MOD(1042848, CEILING(B1/6,1) + 12)

And the number of large boxes:
=FLOOR(A1/36,1) + (B1 > 18)

HTH. :>)