-1

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.

enter image description hereenter image description here

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? enter image description hereenter image description here

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. enter image description here

norbdum
  • 2,361
  • 4
  • 19
  • 23

1 Answers1

0

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:

Add Constraint dialog

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

Solver window (crop)

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?
nekomatic
  • 5,988
  • 1
  • 20
  • 27
  • i did got the order right by changing my code...though not exactly in your way but still the values are different in two excel...how to make the two give same solution. I am using "GRG Nonlinear". Though any optimal solution is okay but how to have it same in two excel workbooks – norbdum Jul 11 '16 at 12:50
  • it doesn't solve the problem may be I need to write my own algorithm ;) – norbdum Jul 12 '16 at 04:45