Results 1 to 10 of 10

Thread: Solver (2000)

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Solver (2000)

    I asked for some help here before re a file I had created utilizing the Solver. Attached is the file-I can't seem to get it to work-the solution is in the red shaded area.
    Any help?
    I have reviewed all of the constrraints, and they all appear to be ok.
    I dunno...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Solver (2000)

    >> I can't seem to get it to work.

    In what way? The solver already seems to have arrived at a solution. Is it incorrect, or not what you expected, or ...?

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Solver (2000)

    It appears to be telling me to buy in months when the prices are higher than in other months when there are no other limiting constraints (i. e., August vs. May).

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Solver (2000)

    I don't know what causes it, but it seems that Excel can't really solve this. Changing one of the input cells can lead to quite different solutions.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Solver (2000)

    Hans,
    Here's a simpler version. Want to take a stab?
    I tried going back through old postings to find where we addressed this issue before (sometime last April, I think), but the archive of old posts appear to only go back to May 1.
    Thanks for your help,
    Jeff

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Solver (2000)

    Hans,
    Heres another, simpler version. By my analysis, it appears at certain minimum levels of 10/31/04 SCQ, the solution brings in a small amount of August purchases, even if I adjust the price to be higher than October.
    Interesting...
    Jeff

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Solver (2000)

    Sorry-forgot to attach the file.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Solver (2000)

    The problem is in the expression you want to minimize. You round the result of each multiplication such as AprilPurchases*April03NYMEX to 2 decimals. This makes different values for the input cells result in the same end value, so the Solver is tricked into believing that a solution has been found.
    If you take out all those ROUND(...,2) functions, the Solver finds a stable solution, both in the workbook you posted today and in the one from yesterday.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Solver (2000)

    Yes-that did the trick, but it is still trying to bring in some August purchases-any idea?
    Thanks for your help; its funny what I thought was an innocent add-in (i. e., rounding) would cause!
    Jeff

  10. #10
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Solver (2000)

    My understanding is that the solver is not a very "smart" algorithm for non-linear problems.

    Let me illustrate. Imagine your problem has just 2 variables and you want to minimize some objective function. If you plotted all possible combinations of your 2 variables on an x and y axis and the corresponding value of the objective function on the z axis, you would get a surface. One can imagine a complex objective function such that the surface would have all kinds of peaks and valleys. Now imagine that you drop a marble onto the surface. Naturally, it will fall into a valley, but it may not fall into the lowest valley. It depends on where you drop the marble. Similarly with the Solver, the initial values in the variable cells determine where Excel "drops the marble". Excel then changes the value of the variables by small amounts to try and find "a direction" to move to find a better answer. If the solver gets stuck in a valley and can't find a better solution nearby, it stops even though there may be a lower valley somewhere else. By using the ROUND function, you've essentially created a surface with lots of plateaus so Solver can't find a direction to move to a better answer.

    Of course, if you extend this analogy to n variables, the surfaces get complex very quickly. I have often found that the solver gives radically different answers depending on the initial values of the variables.

    For more info, check out http://www.frontsys.com This is the company that wrote the solver routine for Excel. They offer enhanced Solver products if you're willing to pay for them.

Posting Permissions

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