none
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

    Wednesday, November 17, 2010 1:52 AM

Answers

  • 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

    • Marked as answer by Bruce Song Tuesday, November 23, 2010 8:55 AM
    Sunday, November 21, 2010 3:50 PM

All replies

  • 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

    Wednesday, November 17, 2010 9:31 PM
  • 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

    Sunday, November 21, 2010 1:05 AM
  • 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

    • Marked as answer by Bruce Song Tuesday, November 23, 2010 8:55 AM
    Sunday, November 21, 2010 3:50 PM