How to find the least inputs for a specific amount of outputs

How can excel find the least amount of inputs needed to produce a specified amount of outputs? To boil it down to the basics:

Need=
100 units of x
200 units of y

From a combination of=
item A that yields 25 units of x and 5 units of y
item B that yields 10 units of x and 15 units of y

Objective = minimize the number of items A & B used (i.e. minimum of (item A)*(item B))

Any ideas?

Also, I'd like to expand the list of needs and items in the future, but tyring to keep it simple for now.

You need linear programming for this. Google "Solver"

http://office.microsoft.com/en-ca/ex...001124595.aspx

As Xx7 said solver could be used but the way your problem is "specified" there is no solution unless you accept a negative amount of item A. Either your yield for A and B are wrong or you must change what you need of x and y.

a specified amount of outputs?

This I take to be that production should be exactly 100 units of x and 200 units of y which is not possible. There is also the question of A and B should they be integer or could one use 1.24A?

Alf

Not exactly but at least that amount. So you need to produce a minimum of 100 units of x and 200 units of y.

A & B have to be integers, so you can't use a fraction of A or B.

The need is to use the least amount of A&B to get the minimum X&Y units needed.

Originally Posted by Alf

As Xx7 said solver could be used but the way your problem is "specified" there is no solution unless you accept a negative amount of item A. Either your yield for A and B are wrong or you must change what you need of x and y.

The numbers above I made up off the top of my head, the actual problem is far more complex involving units of 20 different items and possible inputs of 30+ different items.

What I'd like to understand is how to structure solver to answer the question. I've looked at examples, but they all start in the opposite direction (i.e. you have 2600 bricks and need to make a building of x by y size -- whereas, I need to make 2600 bricks from mud and straw for example.)

Over 1,000,000 thank yous!!!

I read through your example spreadsheet and was able to implement the same on my data set. The results were ... WONDERFUL!!!

Thank you again!

Glad to be of help! Thanks for feedback and rep.

Alf