how to make excel solver give same output each time when there are multiple solutions

Question

I have developed an excel macro one that runs a solver at the supplier site and another at the client. The constraint conditions are created on the fly based on user input. However for more than one solutions I am getting a different answer.

What I can get is that solver constraint equations get ordered according to cell name which are different in two sheets? If I can stop the ordering of constraints this might get solved. How to do it?

Edit I have the constraints in same order though it was a silly way to approach the problem but even after that the solutions are coming different in different excel workbooks.

Answer 1

If you give each of your constraint cells a name via Formulas > Define Name, you can then use that name for the constraint by typing it in the Cell Reference field of the Add Constraint dialog:

The constraints will then be displayed and ordered by name, not by address, in the Solver window:

You can do the same thing if you're configuring the Solver from VBA code, e.g.:

SolverAdd CellRef:="cell1", Relation:=1, FormulaText:="60"

I'm not sure whether this is a good solution to your problem though. If the system of equations you're trying to solve has multiple different solutions then the result of running Solver may depend not only on the constraint settings and starting values but on Solver preferences, the versions of Excel and Solver, and quite possibly other properties of the two systems over which you have no control. Some things you could try include:

• making sure you preload your variable spreadsheet cells with the same starting values before each Solver run, and experiment with what those values should be
• rearranging your system of equations to favour a particular result. For example instead of solving A + B = 100, which has infinitely many equally good solutions, solve to maximise A * B under the constraint that C = 100, where C = A + B.
• writing your own algorithm to find the solution to your equations under the user-entered constraints. If you had to explain to someone how to find a valid solution, what would you tell them - can you convert that into VBA code?