Solver: 2007 vs 2010

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

Martin – thank you for your thoughtful response.

Jack

Reply | Quote