+ Reply to Thread
Results 1 to 9 of 9

Derive optimal mixture formula

  1. #1
    Registered User
    Join Date
    02-06-2014
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2010
    Posts
    48

    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.
    Last edited by SandPounder1; 02-11-2014 at 12:53 PM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,682

    Re: Complex mixture question

    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


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    02-06-2014
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Complex mixture question

    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.
    Attached Files Attached Files
    Last edited by SandPounder1; 02-11-2014 at 01:22 PM. Reason: update file

  4. #4
    Registered User
    Join Date
    02-06-2014
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Derive optimal mixture formula

    Someone told me that this might involve using something in excel called Solver. Anyone familiar with this feature of excel?

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,668

    Re: Derive optimal mixture formula

    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?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Registered User
    Join Date
    02-06-2014
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Derive optimal mixture formula

    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.
    Last edited by SandPounder1; 02-12-2014 at 02:03 PM. Reason: tyops

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,668

    Re: Derive optimal mixture formula

    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.

  8. #8
    Registered User
    Join Date
    02-06-2014
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Derive optimal mixture formula

    Quote Originally Posted by MrShorty View Post
    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
    Time is already "baked in", in thse sense that everything is already reduced to units per hour. So that in 1 hour, I get 1,000 units of raw material x, then in an hour of smelting, I get say 500 units of input y.

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,668

    Re: Derive optimal mixture formula

    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?)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Add personal legend to chart (mixture of textboxes of different colors)
    By DrWhussi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-03-2013, 03:30 PM
  2. mixture of do while and for loops
    By marko3 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-18-2011, 09:13 AM
  3. Replies: 8
    Last Post: 02-18-2011, 10:13 AM
  4. Type mixture error
    By Freddie Mac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-29-2006, 12:45 PM
  5. Data Validation - mixture of options
    By Jack Sheet in forum Excel General
    Replies: 6
    Last Post: 05-20-2005, 11:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1