Derive optimal mixture formula

Is there an excel formula that would tell me the optimal answer to a mixture question. For instance, I need to get a stockpile of outputs in the shortest amount of time possible from ingredients that yield various amounts of these outputs.

So, if I need to obtain a stockpile of:

1,000 units of item 1
2,500 units of item 2
300 units of item 3
1,400 units of item 4

The materials I can possibly use to provduce this output are:

Input 1 yields: 100 item 1, 0 item 2, 50 item 3, 75 item 4
Input 2 yields: 250 item 1, 500 item 2, 25 item 3, 0 item 4
Input 3 yields: 500 item 1, 100 item 2, 0 item 3, 200 item 4

What I also need to take into account is the amount of time it takes to obtain the inputs. so:

in 1 hour I can get 1,000 units of input 1, 800 of input 2, 400 of input 3.

What I need is the optimal combination that takes the least amount of time to get the stockpile I need. the formula(s) would tell me the optimal combination and how long it would take to get them.

I'm wracking my brain on this and have run into the limits of my excel knowledge.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Greatly simplified. Everything is on the same worksheet in this example.

** Updated the file. Made some errors in the earlier one rushing it out before bed.

Solver is often used in optimization problems. As with a lot of problems, there are probably a few different ways to get at the solution.

One difficulty I see is that I am having trouble understanding the details of what you are doing. Is input 1 the same thing as raw material A?

As with many Solver/optimization threads I see discussed around here, the first thing I usually find that I need to do is identify the "objective function" -- the function that I will use in Solver's "set target cell" field. Looking at your problem statement, what is the quantity you need to optimize? time? cost? quantity?

Thanks for the response! Perhaps I can clarify a bit further. Conceptualize it like this:

I can get 1,000 tons of bauxite (raw material) an hour from which I can make 100 tons of aluminum (input) in another hour.
Or I can get 1,500 tons of Corundum (raw material) and hour and make 80 tons of aluminum (input), 10 tons of iron (input), and 15 tons of chromium (input) in anouther hour.
Or 800 tons of ferrochromium (raw material) and hour to get 50 tons of chromium (input) and 30 tons of iron (input) in another hour.

I am trying to make 100,000 cars (let's say). For that I need 400 tons of aluminum, 200 tons of iron, and 150 tons of chromium.

The question is how can I assemble the inputs I need (400 tons Aluminum, 200 tons Iron, 150 tons Chrome) from the raw materials available (bauxite, corundum, ferrochromium) in the shortest amount of time possible.

So what needs to be optimized is the amount of time, the less the better.

PS: the batches in the excel spreadsheet are related to smelting of the materials. You can't smelt just 1 unit of bauxite at a time, you have to smelt a minimum quantity at once.

Hope that helps.

So what needs to be optimized is the amount of time

So, this is what I would use as the objective function. It is not immediately apparent to me from your spreadsheet how you want to calculate this time, but that is where I would start -- how will you calculate total time used to obtain inputs from raw materials. Is this what is calculated in E24?

Once you have your objective function identified and programmed into the spreadsheet, the next step would be to identify any constraints for the problem. In this case, these constraints will probably be the final makeup of the stockpile, so you will need to figure out how to calculate the makeup of the stockpile for the raw materials processed.

I can see those rates in the spreadsheet, but don't you need to calculate, from those rates, the total time needed to produce a certain amount of each input? for example, knowing that you can produce y is produced at the rate of 500/hr, don't you need to somewhere calculate "how many hours does it take to produce 4500 units?" [total produced = rate * time ---- time = total produced/rate]

Or maybe you are trying to perform the calculations on a "differential" or rate basis ("I produce y at 500 units per hour, so how many final products can I make per hour" kind of approach?)