Results 1 to 15 of 18
Thread: Odd Solver Behavior (Excel 2003)

20070326, 23:47 #1
 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?

20070326, 23:59 #2
 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 builtin 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.

20070327, 00:25 #3
 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

20070327, 02:33 #4
 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

20070328, 18:30 #5
 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

20070328, 19:02 #6
 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"

20070328, 22:33 #7
 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

20070328, 23:04 #8
 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.

20070329, 01:06 #9
 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

20070329, 08:51 #10
 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?

20070329, 15:35 #11
 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" writeup on all the things solver can do and who to make it do it.
Marty

20070329, 15:41 #12
 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.

20070401, 12:33 #13
 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

20070401, 12:43 #14
 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 fixedwidth font. But look out  lines will not be wrapped automatically.
Or put the code between <!t>Code:<!/t> and <!t>

20070401, 13:27 #15
 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