optimization-selecting a group with constraints

[chandran19]

I have a problem as follows. It is mathematical and can be put in exce
macro

"THERE ARE 3 NO OF ASSEMBLIES EACH REQUIRING SOME COMPONENTS(A,B,C)
AS FOLLOWS(QTY NOS ARE MINIMUM)
FOR EXAMPLE ASSEMBLY1 REQUIRES COMPONENT A FOR A MINIMUM OF 2 NOS
B A MIN OF 3 AND C A MIN OF 10

A B C
ASSEMBLY1 2 3 10
ASSEMBLY2 3 6 2
ASSEMBLY3 1 2 5

These components A,B,C are available in three pcckets as follows

A B C
packet1 4 5 15
packet2 2 5 5
packet3 1 2 5

NOW the requirements AND CONDITIONS is as follows

1.WHEN I OPEN A PACKET FOR EXAMPLE PACKET1 FOR MAKING ASSEMBLY 1
IT CAN SATISFY ASSEMBLY1 BUT THE COMPONENTS ARE IN EXCESS. IN
THAT CASE EITHER PACKET2 OR 3 MAY DO IT WITH LESS WASTAGE O
COMPONENTS

2.IF NEITHER PACKET 1,2 OR 3 CAN BE SELECTED CAN THE FOLLOWING
GIVE THE MINIMUM WASTAGE

X1*PACKET1+Y1*PACKET2+Z1*PACKET3
X1,Y1,Z1 ARE INTEGERS.

WHAT I MEAN ABOVE IS WILL X QTY OF PACKET1,Y QTY OF PACKET2 AND Z
QTY OF PACKET3 YIELD LESS WASTAGE

I HAVE TRIED THIS IN EXCEL AND SUCCESSFUL FOR CONDITION 1
BUT I DONT KNOW HOW TO PUT THIS FOR CONDITION

 

[Tom Ogilvy]

Look in the Examples\Solver subdiretory for solvsamp.xls. This appears to
be a classic linear programming type optimization problem - so solver
(Tools=>Solver -- if you don't have that option, install Solver from your
office CD).

On my xl97 version, it is found at:
C:\Program Files\Microsoft Office\Office\Examples\Solver

 

[Tushar Mehta]

As Tom has already pointed out, check out Solver.

That said, you also need to look at completely define the problem.
Unless there is some additional criterion, the trivial solution to the
existing problem is open zero packets, make zero assemblies, and have
zero waste. You also need to define what it means to have waste. Are
A, B, and C equally valuable? Or is there a economic ranking to
wasting A, B, or C?

Finally, you don't have to type everything in uppercase. In case you
did not know, by convention it is the equivalent of shouting.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions