Excel Optimization Using Solver & Iterative Calculation

Question

Hi,

I have a couple of questions regarding the iterative calculation (Excel Options - Formulas - Enable iterative calculation) and the use of the Excel solver in Excel 2007. First, I have a developed an excel add in which allows the user to build steady state system models which include feedback, and because of the inherent loop this creates circular references, and the iterative calculation tick box sorts this one out.

However this is just step one (i.e. reaching a steady state), the second step is to optimize the system using Excel Solver. However I have found that the solver does not appear to wait until the first iteration loop is complete - in fact the decision variable is not changed at all and it promptly stops saying solution found. Does this mean that the solver + iterative calculation cannot be used together?

Second, because I know the structure of the system I have already designed an iterative solver which is much faster than the Excel iterative solver, but I am not sure how to implement this in Excel. Is there a way to create your own method / function of handling circular references in Excel? This would then allow me to setup the optimization problem much more succinctly as well.

Regards,

Jonathan

Answer 1

Concerning your first question it's hard to suggest anything without being able to see or recreate what you have. How and when does Solver get called. Could the calculation order make a difference, only a guess but it can make a difference, particularly with iteration.

When you say you've designed your own solver do you mean with code, eg VBA. If so can you simply dump the results to cells, or maybe use a combination of code with instructions to Excel to do some aspects of the work. Again hard to comment without knowing what you mean.

Regards,
Peter Thornton

Answer 2

Hi Peter, thanks for your reply.

Firstly, the optimization solver gets called manually using the little user interface window you get when you click solver on the ribbon. For example purposes I select the total economic cost of the model (the sum of a few cells), choose one free variable (i.e. a model power), constrain it to within physical bounds and click solve.

What appears to happen is the solver is free to change the free variable, but changing it does not invoke the excel iterative solver to then resolve the entire sheet. The effect of this is that no matter what the solver changes the free variable cell to, it has no affect on the cost function and thus exits early.

I am just wondering why the iterative solver does not get called when a cell changes when the optimization solver (solver.xlam) is running.

Secondly, I have my own solver in C# but it is implementation I am confused about. If you create a circular reference in Excel - then you get an error box if iterative calculation is not enabled (as described in my first post). If iterative calculation is enabled, then the solver will attempt to reach a steady state (i.e. maximum change of default 0.001) between all cells in the 'circle'. My question is I don't want to use the built-in Excel iterative solver (it is a general problem solver), so how can I implement my own 'iterative solver' to prevent the circular reference error from occuring?

Regards,
Jonathan

Answer 3

On the last bit, how to prevent the circular reference message, you can change the settings with code to prevent the error dialog (see below)

If I follow, you want to exploit both Excel's normal iterative solver and the Solver, addin both controlled with code (your C#), possibly concurrently. Could you devise a VBA example to recreate the problem, the minimum necessary, something simple along the lines of the following -

[vbnet]
Sub Setup()
    With Application
       .Iteration = True
       .MaxIterations = 1
       .MaxChange = 0.1
   End With
    Range("A1") = 0
   Range("A2").Formula = "=A1+A2"
   Range("B1").Formula = "=A1+1"
    SolverTest

End Sub

Sub SolverTest()
' with Solver.xla(m) loaded,
' Tools, References, tick SOLVER
    SolverOk SetCell:="$B$1", ValueOf:="2", ByChange:="$A$1"
   SolverSolve True

' record a macro using Solver for more arguments if necessary

End Sub

Sub ResetDefault()
' probably better to trap settings first and restore as-was
   With Application
       .Iteration = False
       .MaxIterations = 100
       .MaxChange = 0.001
   End With
End Sub
[vbnet]

Not sure if relevant but see Charles Williams' page for more on what I mentioned about the calculation order (cell location can make a difference to results with iteration)

http://www.decisionmodels.com/calcsecretsc.htm

Regards,
Peter Thornton