combinations of balloons

[Talon52]

I have a major problem. I need a formula to come up with the combinations of balloons for my company.

I have 5267 Yellow Balloons

I have 5590 Pink Balloons

I have 5127 Green Balloons

I want them in 8 packs consisting of 3 of one color/3 of a second color/and 2 of the third.

I have to have a total of 1900 packs, no less.

I need this as quickly as possible!!!!

Thanks,
Talon

 

[Andrew Fergus]

Hi

I think your number of combinations isn't right. I can only see 3 possible combinations of coloured balloons to make packets of 8, being:

YYYPPPGG
YYYPPGGG
YYPPPGGG

Assuming that is correct, divide the total number of required bags by 3 and make 633 of each of these combinations. You will use 5,064 of each coloured balloon for the first 1899 packs and you have enough spare balloons in each colour to make the final bag any combination you like.

I trust that helps and hopefully I haven't seriously misunderstood your question.

Andrew

 

[Andrew Fergus]

Sorry - I misread '8 packs' as being 8 combinations of colours, rather than packets containing 8 balloons. It doesn't affect the answer I provided, but I'm not sure if I am understanding the question 100% correctly. You seem to have a very specific number of coloured balloons and a specific number (ie 1900) of packets, and according to the solution I provided, a whole lot of balloons left over.

 

[shift-del]

and according to the solution I provided, a whole lot of balloons left over.

Actually there are so many left over that ANY combination resulting in exactly 1900 packs is valid.

 

[mikerickson]

I think I have a solution
Cells C2:E2 hold the original count of balloons on hand

Cell C4 holds the formula
=IF(AND(MATCH(C2,$C2:$E2,0)=COLUMN(A4), C2=MIN($C2:$E2)), 2, 3)
which, when dragged right, determines which type of 8 pak to make. (2-3-3, 3-2-3 or 3-3-2)
Cell C5 =$B5*C4, calculates how many yellow balloons are needed to make the number of packs in B5. This is dragged right.
Cells C6:E6 show how many balloons remain after making B5 8-paks of type C4:E4

The formula in B5 is
=MIN(MAX($C2:$E2)-MIN($C2:$E2), INT(C2/C4),INT(D2/D4),INT(E2/E4))
which is the difference between the most and least collor of balloons on hand in C2:E2.
Making this many 8-paks of the indicated type, the remaing count of the most and the least avaliable colors will be equal

copying A4:E6 to A8 repeats this process.

The types in C12:E12, C16:E16, C20:E20 are hard coded.
The formula in B13 is = INT(C10/8), B17 and B 21 have = B13

In the summary section to the right, the end count of each type of 8-pak is shown by putting this in H9 and dragging right.
=SUMIF(C$4:C$20, 2, $B$5:$B$21)

Considering breakage, a total of 1,998 8-paks should work.

<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>A</b><td width=120><b>B</b><td width=25><b>C</b><td width=25><b>D</b><td width=25><b>E</b><td width=25><b>F</b><td width=125><b>G</b><td width=75><b>H</b><td width=75><b>I</b><td width=75><b>J</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Yellow<td align="left" bgcolor=#FFFFFF>Pink<td align="left" bgcolor=#FFFFFF>Green<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Baloons In stock<td align="right" bgcolor=#FFFF99>5267<td align="right" bgcolor=#FFFF99>5590<td align="right" bgcolor=#FFFF99>5127<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Total Baloons at start<td align="right" bgcolor=#FFFFFF>15984<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>total packs made<td align="right" bgcolor=#CC99FF>1998<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>4</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>lot type<td align="right" bgcolor=#FFFFFF>3<td align="right" bgcolor=#FFFFFF>3<td align="right" bgcolor=#FFFFFF>2<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>5</b><td align="left" bgcolor=#FFFFFF>packs-<td align="right" bgcolor=#FF99CC>463<td align="right" bgcolor=#FFFFFF>1389<td align="right" bgcolor=#FFFFFF>1389<td align="right" bgcolor=#FFFFFF>926<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>total baloons needed<td align="right" bgcolor=#FFFFFF>15984<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>6</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>baloons remaining<td align="right" bgcolor=#FFFF99>3878<td align="right" bgcolor=#FFFF99>4201<td align="right" bgcolor=#FFFF99>4201<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>7</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>8</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>lot type<td align="right" bgcolor=#FFFFFF>2<td align="right" bgcolor=#FFFFFF>3<td align="right" bgcolor=#FFFFFF>3<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Type of pack<td align="left" bgcolor=#FFFFFF>YY-PPP-GGG<td align="left" bgcolor=#FFFFFF>YYY-PP-GGG<td align="left" bgcolor=#FFFFFF>YYY-PPP-GG</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>9</b><td align="left" bgcolor=#FFFFFF>packs-<td align="right" bgcolor=#FF99CC>323<td align="right" bgcolor=#FFFFFF>646<td align="right" bgcolor=#FFFFFF>969<td align="right" bgcolor=#FFFFFF>969<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>count of packs<td align="right" bgcolor=#FFFFFF>727<td align="right" bgcolor=#FFFFFF>404<td align="right" bgcolor=#FFFFFF>867</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>10</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>baloons remaining<td align="right" bgcolor=#FFFF99>3232<td align="right" bgcolor=#FFFF99>3232<td align="right" bgcolor=#FFFF99>3232<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>11</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>total packs<td align="right" bgcolor=#CC99FF>1998<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>12</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>3<td align="right" bgcolor=#FFFFFF>3<td align="right" bgcolor=#FFFFFF>2<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>13</b><td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FF99CC>404<td align="right" bgcolor=#FFFFFF>1212<td align="right" bgcolor=#FFFFFF>1212<td align="right" bgcolor=#FFFFFF>808<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>14</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>baloons remaining<td align="right" bgcolor=#FFFF99>2020<td align="right" bgcolor=#FFFF99>2020<td align="right" bgcolor=#FFFF99>2424<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>15</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>16</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>3<td align="right" bgcolor=#FFFFFF>2<td align="right" bgcolor=#FFFFFF>3<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>17</b><td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FF99CC>404<td align="right" bgcolor=#FFFFFF>1212<td align="right" bgcolor=#FFFFFF>808<td align="right" bgcolor=#FFFFFF>1212<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>18</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>baloons remaining<td align="right" bgcolor=#FFFF99>808<td align="right" bgcolor=#FFFF99>1212<td align="right" bgcolor=#FFFF99>1212<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>19</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>20</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>2<td align="right" bgcolor=#FFFFFF>3<td align="right" bgcolor=#FFFFFF>3<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>21</b><td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FF99CC>404<td align="right" bgcolor=#FFFFFF>808<td align="right" bgcolor=#FFFFFF>1212<td align="right" bgcolor=#FFFFFF>1212<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>22</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>baloons remaining<td align="right" bgcolor=#FFFF99>0<td align="right" bgcolor=#FFFF99>0<td align="right" bgcolor=#FFFF99>0<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
</table>

 

[pgc01]

Hi Mark

I cannot understand this problem.

I tried 2 interpretations, but none made sense

Interpretation 1:

You want to use all the baloons to make the packs. In that case the total number of packs is:

(5267 + 5590 +5127) / 8 = 1998

Since you say that the total number of packs is 1900, this problem has no solution.

Interpretation 2:

You want to use some of the baloons to make the 1900 packs. In that case, using Andrew's logic you use 633 packs of the 3 types he defined + 1 pack of any type.

Conclusion:
Neither of the interpretations results in a problem that needs excel.
Can you explain?

Remark: I'm curious. Can you explain why is this important to your company?

 

[Zack Barresse]

Sounds like homework...

 

[mikerickson]

Sounds like math homework (not programming). But..I'm surprised. It looks like it has a unique solution. It's not indeterminate system of equations.

My solution above is not only poorly expressed, but its not a very concise approach. I should work out a matrix based solution and post it.