Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Solver: 2007 vs 2010

    A colleague developed and Excel 2007 spreadsheet that uses the Solver Add-in. His computer was recently upgraded to Excel 2010 while mine remained on 2007. We both made copies of the same file and ran the Solver under exactly the same conditions, and received different answers. It appears that the 2007 version is correct -- it has lower costs in a cost-minimization objective function. Both computers respect all the constraints. We tested on two additional computers and found exactly the same results. Solver in 2007 and 2010 return different results. The differences were non-trivial $26.6M vs $26.0M.

    Are there any known issues with the solver in Excel 2010? Are there any workarounds, or non-obvious configuration settings?

    Grasping at straws...

    Thanks for any insight.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    For linear functions, Solver should come quickly to an optimal solution which is independent of the version of Excel you are using.

    As your proposition becomes more complex - non-linear, non-smooth, convex and so on - the finding of an optimal solution is limited by both the solving time available (it isn't hard to write a model which would take billions of years to optimise in a deterministic way) and the inherent solvability.

    In these circumstances, Solver will usually provide you with a better answer but may not necessarily progress to the best answer.

    To be pedantic (= precise), you don't know that 2007 Solver gave you the "correct" (= best) answer, just that it was better than the 2010 Solver.

    In their efforts to progress further along the road from better to best, both additional and improved strategies have been introduced into the Solver add-in for Excel 2010 - so for complex propositions I would expect different answers to your optimisation. It is perverse that the 2010 version has given you a worse result.

    Solver is actually a 3rd party tool, provided by Frontline Systems, and I expect you can get further information from them.

    I am concerned that maybe you think that optimisation is a deterministic process when, for many models, it is not. Whilst an optimum answer will always exist, in complex models there is simply no way of being certain that you have found it.

    However, as long as both "solutions" conform to your constraints (and I expect that they do, in the case of Solver), you have stumbled on another form of optimisation, namely comparing the results of different tools. And you've also confirmed that not all "progress" is forwards.
    Last edited by MartinM; 2012-02-11 at 09:49.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Martin - thank you for your thoughtful response.


    Jack
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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