Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Odd Solver Behavior (Excel 2003)

    I have a 30 year cash flow in which I would like to solve for required dues to insure I have at least a specified ending balance. I've written a macro to it loop through each of the 30 years and, if the ending balance is less than the specified amount, run Solver to find the required increased dues amount. Each time I run solver I'm providing a range of cells to be adjusted. The range starts with the cash flow beginning year and ends with the current year (e.g. the year in which the ending balance is less than the specified amount.) So for example if the 3rd year ending balance was less than the specified amount, the dues for the first three years would be changed.

    I was anticipating the Solver would spread the change evenly over the periods specified. I've found in about 1/3 the cases that is not the case, the amount is being spread very unevenly. Any thoughts on why this occurs? Are there some settings I should be looking at (I'm currently using no constraints and have not changed any of the options).

    Also, as I searched through the Solver posts, I've found some very interesting ways solver has been used by setting constraints. As I'm new to using Solver, can anyone recommend a source for detailed documentation?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Odd Solver Behavior (Excel 2003)

    The Solver has no built-in preference for spreading values evenly. You could add constraints for that.

    Solver Tutorial for Optimization Users is from the company that developed Solver.
    Searching Google for excel solver tutorial will turn up more material.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Odd Solver Behavior (Excel 2003)

    One way to reduce teh variability among a group of cells is to set a constraint to make the std dev of the cells less than a particular target value.

    Steve

  4. #4
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Odd Solver Behavior (Excel 2003)

    Hans,

    Thanks for the insight. As I mentioned, 2/3 of my results were spread evenly and that seemed to make sense for a simple case. I have read through the tutorial, but didn't find it all that helpful. Maybe I'm just to green on optimization. I guess what I'm really looking for is a good Tips & Tricks document. I'll see what Google comes up with.

    Marty

  5. #5
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Odd Solver Behavior (Excel 2003)

    I've run accross another issue which has me stumped. I used macro recorder to create the code below. I then added the SolveReset funciton to clear the constraints from any prior executions. During the recording process Solver worked just fine. There after, I get an error on execution. "Solver: An unexpected internal error occurred, or available memory was exausted." I've tried taking the SolverReset statement out ... still get the error message. I can manually run solver with the same settings as in the macro and it works without a problem ... even after the macro has failed.

    Appreciate any thoughts and suggestions,
    Marty


    Sub SolveMinMaxEndingBalances()
    On Error GoTo Err_SolveMinMaxEndingBalances
    SolverReset
    SolverOk MaxMinVal:=3, _
    ValueOf:="", _
    ByChange:="$C$8:$AE$8"
    SolverAdd CellRef:="$C$13:$AE$13", _
    Relation:=1, _
    FormulaText:="Max_Ending_Balances"
    SolverAdd CellRef:="$C$13:$AE$13", _
    Relation:=3, _
    FormulaText:="Min_Ending_Balances"
    SolverOk MaxMinVal:=3, ValueOf:="", ByChange:="$C$8:$AE$8"
    SolverSolve UserFinish:=True
    Exit_SolveMinMaxEndingBalances:
    Exit Sub
    Err_SolveMinMaxEndingBalances:
    MsgBox Err.Description, , "SolveMinMaxEndingBalances"
    Resume Exit_SolveMinMaxEndingBalances
    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Odd Solver Behavior (Excel 2003)

    Try inserting this line at the beginning of the macro:

    Application.Run "Solver.xla!Auto_Open"

  7. #7
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Odd Solver Behavior (Excel 2003)

    Hans,

    Thanks for the suggestion ... unfortunately same results.

    Marty

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Odd Solver Behavior (Excel 2003)

    We'd have to see the workbook.

  9. #9
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Odd Solver Behavior (Excel 2003)

    Hans,

    Thanks for taking a look at it. The macro "SolveMinMaxEndingBalances" is the one giving me the problem.

    Marty
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Odd Solver Behavior (Excel 2003)

    Frankly, I don't understand what you're trying to do. I've only used Solver in situations where you try to minimize or maximize a target cell.
    Your code specifies MaxMinVal:=3, where 3 = Match a specific value - how can you match a specific value if there is no target cell and the target value is blank?

  11. #11
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Odd Solver Behavior (Excel 2003)

    Hans,

    Before making my original post on this subject I did a Lounge search on Solver. One of the posts expalined and posted a demo spreadsheet of how to use a set of constraints in conjuntion with a range of cells to be modified. When run in manual mode this technique works great. I can solve for 29 years of Reserve Assessments by constraining 29 years of ending balances to be greater/equal to the minimum set of values and less/equal to the minimum set of values. All the ranges must be of the same size.

    Being new to Solver myself, I don't know how this works, it just seems to except in macro form. As I mentioned before, I would love to find a good "Tips/Tricks" write-up on all the things solver can do and who to make it do it.
    Marty

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Odd Solver Behavior (Excel 2003)

    I'm afraid I don't know enough about Solver, so I cannot help here. Sorry.

  13. #13
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Odd Solver Behavior (Excel 2003)

    Marty,

    Sorry for replying late, but my laptop is under repair, so I am using a different PC that does not contain all my standard info. You were referring to my implementation of a solver problem where you only have constraints which have to be satisfied, i.e. no objective function. That particular macro looked as follows (sorry I don't know how to change the font the way Hans V and others always do):
    ==========
    'Get rid of LHS<=RHS constraint errors, varying all variables (this is just a comment line, all other statements start with "Solver...", the _ (underscore) indicates a continuation of the statement on a new line. Lines staring with ' (apostrophe) indicate a comment line and are ignored during execution)
    SolverGRGOptions MaxTime:=300, Iterations:=200, Precision:=0.0001, Convergence _
    :=0.00001, StepThru:=False, Scaling:=True, AssumeNonneg:=True, BypassReports:= _
    False, RecognizeLinear:=True, Estimates:=2, Derivatives:=2, SearchOption:=2 <<these are the options settings>
    SolverAdd CellRef:="LHS", Relation:=1, FormulaText:="RHS"<the are the LHS and RHS constraint range names, in this case also called LHS and RHS and their relation. Relation = 1 which means that they have to be the same>>
    SolverOk MaxMinVal:=0, ValueOf:=0, ByChange:="Adj_Irr,Adj_Flows, Adj_TPS", Engine _
    :=1, EngineDesc:="Standard GRG Nonlinear"<<MaxMinVal:=0 means there is no objective function, the changeable cells or range are indicated by their names between "">>
    SolverModel SolveWith:=1 <<final solve command>>
    SolverSolve (True)
    =================
    Now this is the coding for the Frontsys PremiumSolver AddIn and thus only applicable for the Standard Excel Solver if you change a few things. Unfortunately I cannot demonstrate you what because of my laptop problem. I put a few comments between <<>> inside the coding. You would have to remove these including <<>>, anyhow.

    I attach the VBA commands to program the standard solver in a next message. Hopefully you can solve your problem.

    Regards, Teunis

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Odd Solver Behavior (Excel 2003)

    You can place the code between <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags. This will preserve indentation and use a fixed-width font. But look out - lines will not be wrapped automatically.

    Or put the code between <!t>
    Code:
    <!/t> and <!t>
    <!/t> tags. This merely applies a fixed-width font, it doesn't preserve indentation. You can use <!t>[tab]<!/t> tags for that.

  15. #15
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Odd Solver Behavior (Excel 2003)

    Hans,

    Thanks

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •