How to increase the trials for solver in data tab?

[Mosen87]

Hello
I have a target function changing with two other variables. I want the target function be zero by changing the two variables but I don't get the right solution. Is there a way I can increase the trails Excel does to find the solution? I see at the bottom right of excel, It counts the trials up to 21 in my case.
Secondly, In case It cannot find any solution, Is there a way to make the target function a number close to zero not exactly zero, so It might help find a solution.
Thanks

 

[mrshl9898]

Can you take a screen shot? Not sure what you mean

 

[Eric W]

Can you post your macro?

 

[Mosen87]

Can you post your macro?

Sure, This code in fact assign the kchange and vchange for variables and make the obj function to zero. Please see Sub Opter
Sub starter()
'Application.ScreenUpdating = False
donum = -15
mini = 10
off = 57
con = 0
SelNum = -57
Sheets("Solver").Select
Range("B66").Select
Call ValSet
Call Opter
Range("B17").Select
Call valOf

Range("B66").Select
Call Opter



Range("B66").Select
con = 1
kchange = -0.5
vchange = 0.5
Call Opter
Range("B17").Select
'Call valOf
Range("B66").Select

kchange = 1
vchange = 1
Call Opter
Range("B17").Select
'Call valOf
Range("B66").Select


kchange = 0.5
vchange = -0.5
Call Opter
Range("B17").Select
'Call valOf
Range("B66").Select

kchange = -0.5
vchange = -0.5
Call Opter
Range("B17").Select
'Call valOf
Range("B66").Select

kchange = 0.5
vchange = 0.5
Call OpterUnconst
Range("B17").Select
'Call valOf
Range("B66").Select

kchange = -0.5
vchange = 0.5
Call OpterUnconst
Range("B17").Select
'Call valOf
Range("B66").Select

kchange = 0.5
vchange = -0.5
Call OpterUnconst
Range("B17").Select
'Call valOf
Range("B66").Select

kchange = 10
vchange = 1
Call OpterUnconst
Range("B17").Select
'Call valOf
Range("B66").Select

kchange = 5
vchange = 1
Call OpterUnconst
Range("B17").Select
'Call valOf
Range("B66").Select

kchange = 1
vchange = 5
Call OpterUnconst
Range("B17").Select
'Call valOf
Range("B66").Select

kchange = 2
vchange = 50
Call OpterUnconst
Range("B17").Select
'Call valOf
Range("B66").Select

kchange = -5
vchange = 0.5
Call OpterUnconst
Range("B17").Select
'Call valOf
Range("B66").Select

kchange = -10
vchange = 10
Call OpterUnconst
Range("B17").Select
'Call valOf
Range("B66").Select

kchange = -10
vchange = 1
Call OpterUnconst
Range("B17").Select
'Call valOf
Range("B66").Select

kchange = 1
vchange = 10
Call OpterUnconst
Range("B17").Select
'Call valOf
Range("B66").Select


kchange = -0.5
vchange = -0.5
Call OpterUnconst
Range("B17").Select
'Call valOf



con = 0
Range("N66").Select
Call Opter
Range("N66").Select

con = 1
kchange = 1
vchange = 1
Call Opter
Range("N66").Select

'con = 1
kchange = 0.123
vchange = 0.321
Call Opter
Range("N66").Select

kchange = -0.5
vchange = 0.5
Call Opter
Range("N66").Select

kchange = 0.5
vchange = -0.5
Call Opter
Range("N66").Select

kchange = -0.5
vchange = -0.5
Call Opter
Range("N66").Select

kchange = 0.5
vchange = 0.5
Call OpterUnconst
Range("N66").Select

kchange = -0.5
vchange = 0.5
Call OpterUnconst
Range("N66").Select

kchange = 0.5
vchange = -0.5
Call OpterUnconst
Range("N66").Select

kchange = 5
vchange = 1
Call OpterUnconst
Range("N66").Select

kchange = 1
vchange = 5
Call OpterUnconst
Range("N66").Select

kchange = -0.5
vchange = -0.5
Call OpterUnconst


Range("AB66").Select
con = 0
Call Opter3
Range("AB66").Select
con = 1
kchange = -3
Call Opter3


Range("AK66").Select
Call Opter4

con = 0
mini = 1
donum = 2
off = 3

Range("BB15").Select
Call valOf2

Range("BP15").Select
Call valOf2

Range("AV24").Select
Call OpterUnconstr

Range("AV12").Select
Call OpterUnconstr

Range("BJ24").Select
Call OpterUnconstr

Range("BJ12").Select
Call OpterUnconstr

con = 0
mini = 0
donum = -2
off = 49
SelNum = -49
Range("BP54").Select
Call OpterUnconstr

Range("BB54").Select
Call OpterUnconstr
SolverReset
End Sub
Sub ValSet()
'Application.ScreenUpdating = False
Range("B9:J10, AB9:AH9").Value = 0.5
Range("N9:O10, Q9:U10, W9:W10, X10").Value = -0.5
Range("P9").Value = 2
Range("P10").Value = -2.5
Range("AK9:AP9").Value = 0.75
Range("V9").Value = -0.005
Range("v10").Value = 0.05
Range("x9").Value = -0.05
End Sub
Sub Opter()
'Application.ScreenUpdating = False
'Call Module1.Iterations
'Print ActiveCell.Address
Do While IsEmpty(ActiveCell.Offset(donum, 0)) = False
If mini = 1 Then
Call selecterMini
Else
Call selecter
End If
Set changev = Selection
ActiveCell.Offset(off, 0).Select
Debug.Print ActiveCell.Address

On Error GoTo erhand
If ActiveCell.Value > 950 Then
If con = 1 Then
Range(kvar).Value = kchange
Range(vVar).Value = vchange
Else
End If

'Application.Run "SolverReset"
'Application.Run "SolverOk", "ActiveCell.Address", 3, "0", "changev.Address"
'Application.Run "SolverAdd", "kvar", 1, "Range(kvar).Offset(-1, 0).value"
'Application.Run "SolverAdd", "kvar", 3, "Range(kvar).Offset(-2, 0).value"
'Application.Run "SolverAdd", "vvar", 1, "Range(vVar).Offset(-4, 0).value"
'Application.Run "SolverAdd", "vvar", 3, "Range(vVar).Offset(-5, 0).value"
'Application.Run "SolverSolve", True
SolverReset
SolverOptions AssumeNonNeg:=False, Derivatives:=2
SolverOk SetCell:=ActiveCell.Address, _
MaxMinVal:=3, _
ValueOf:=0, _
ByChange:=changev.Address
SolverAdd CellRef:=kvar, Relation:=1, FormulaText:=Range(kvar).Offset(-1, 0).Value
SolverAdd CellRef:=kvar, Relation:=3, FormulaText:=Range(kvar).Offset(-2, 0).Value
SolverAdd CellRef:=vVar, Relation:=1, FormulaText:=Range(vVar).Offset(-4, 0).Value
SolverAdd CellRef:=vVar, Relation:=3, FormulaText:=Range(vVar).Offset(-5, 0).Value
SolverSolve UserFinish:=True
ActiveCell.Offset(0, 1).Select
Else
erhand:
Resume Next
ActiveCell.Offset(0, 1).Select
End If
Loop
'Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub valOf()
'Application.ScreenUpdating = False
Do While IsEmpty(ActiveCell.Offset(-2, 0)) = False
If ActiveCell.Value = "Run Opt.1" Then
SolverReset
SolverOk SetCell:=ActiveCell.Offset(1, 0).Address, _
MaxMinVal:=3, _
ValueOf:=0, _
ByChange:=Selection.Offset(2, 0).Address
SolverSolve UserFinish:=True
ActiveCell.Offset(0, 1).Select
Else
ActiveCell.Offset(0, 1).Select
End If
Loop
End Sub
Sub valOf2()
'Application.ScreenUpdating = False
Do While IsEmpty(ActiveCell.Offset(-3, 0)) = False
If ActiveCell.Value = "Run Opt.1" Then
SolverReset
SolverOk SetCell:=ActiveCell.Offset(1, 0).Address, _
MaxMinVal:=3, _
ValueOf:=0, _
ByChange:=Selection.Offset(2, 0).Address
SolverSolve UserFinish:=True
ActiveCell.Offset(0, 1).Select
Else
ActiveCell.Offset(0, 1).Select
End If
Loop
End Sub
Sub OpterUnconst()
'Application.ScreenUpdating = False
'Call Module1.Iterations
Do While IsEmpty(ActiveCell.Offset(donum, 0)) = False
If mini = 1 Then
Call selecterMini
Else
Call selecter
End If
Set changev = Selection
ActiveCell.Offset(off, 0).Select
On Error GoTo erhand
If ActiveCell.Value > 950 Or ActiveCell < -500 Then

If con = 1 Then
Range(kvar).Value = kchange
Range(vVar).Value = vchange
Else
End If

SolverReset
SolverOptions AssumeNonNeg:=False, Derivatives:=2
SolverOk SetCell:=ActiveCell.Address, _
MaxMinVal:=3, _
ValueOf:=0, _
ByChange:=changev.Address
SolverAdd CellRef:=kvar, Relation:=1, FormulaText:=Range(kvar).Offset(-1, 0).Value + 2
SolverAdd CellRef:=kvar, Relation:=3, FormulaText:=Range(kvar).Offset(-2, 0).Value - 2
SolverAdd CellRef:=vVar, Relation:=1, FormulaText:=Range(vVar).Offset(-4, 0).Value + 15
SolverAdd CellRef:=vVar, Relation:=3, FormulaText:=Range(vVar).Offset(-5, 0).Value - 15
'SolverAdd CellRef:=kvar, Relation:=1, FormulaText:=Range(kvar).Offset(-1, 0).value
'SolverAdd CellRef:=kvar, Relation:=3, FormulaText:="0.0001"
'SolverAdd CellRef:=vVar, Relation:=1, FormulaText:=Range(vVar).Offset(-4, 0).value
'SolverAdd CellRef:=vVar, Relation:=3, FormulaText:="0.0001"
SolverSolve UserFinish:=True
ActiveCell.Offset(0, 1).Select
Else
erhand:
Resume Next
ActiveCell.Offset(0, 1).Select
End If
Loop
'Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub OpterUnconstr()
'Application.ScreenUpdating = False
'Call Module1.Iterations
Do While IsEmpty(ActiveCell.Offset(donum, 0)) = False
If mini = 1 Then
Call selecterMini
Else
Call selecter
End If
Set changev = Selection
ActiveCell.Offset(off, 0).Select
On Error GoTo erhand
If ActiveCell.Value > 0.95 Or ActiveCell < -0.5 Then

If con = 1 Then
Range(kvar).Value = kchange
Range(vVar).Value = vchange
Else
End If

SolverReset
SolverOptions AssumeNonNeg:=False, Derivatives:=2
SolverOk SetCell:=ActiveCell.Address, _
MaxMinVal:=3, _
ValueOf:=0, _
ByChange:=changev.Address
'SolverAdd CellRef:=kvar, Relation:=1, FormulaText:=Range(kvar).Offset(-1, 0).value + 2
'SolverAdd CellRef:=kvar, Relation:=3, FormulaText:=Range(kvar).Offset(-2, 0).value - 2
'SolverAdd CellRef:=vVar, Relation:=1, FormulaText:=Range(vVar).Offset(-4, 0).value + 15
'SolverAdd CellRef:=vVar, Relation:=3, FormulaText:=Range(vVar).Offset(-5, 0).value - 15
'SolverAdd CellRef:=kvar, Relation:=1, FormulaText:=Range(kvar).Offset(-1, 0).value
'SolverAdd CellRef:=kvar, Relation:=3, FormulaText:="0.0001"
'SolverAdd CellRef:=vVar, Relation:=1, FormulaText:=Range(vVar).Offset(-4, 0).value
'SolverAdd CellRef:=vVar, Relation:=3, FormulaText:="0.0001"
SolverSolve UserFinish:=True
ActiveCell.Offset(0, 1).Select
Else
erhand:
Resume Next
ActiveCell.Offset(0, 1).Select
End If
Loop
'Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Sub Opter3()
'Application.ScreenUpdating = False
'Call Module1.Iterations
Do While IsEmpty(ActiveCell.Offset(-10, 0)) = False
Call selecter3
Set changev = Selection
ActiveCell.Offset(57, 0).Select
On Error GoTo erhand
If ActiveCell.Value > 950 Or ActiveCell.Value < -0.1 Then
'MsgBox ActiveCell.value
If con = 1 Then
Range(kvar).Value = kchange
Else
End If

SolverReset
SolverOptions AssumeNonNeg:=True, Derivatives:=2
SolverOk SetCell:=ActiveCell.Address, _
MaxMinVal:=3, _
ValueOf:=0, _
ByChange:=kvar
SolverAdd CellRef:=kvar, Relation:=1, FormulaText:=Range(kvar).Offset(-1, 0).Value
SolverAdd CellRef:=kvar, Relation:=3, FormulaText:=Range(kvar).Offset(-2, 0).Value
SolverSolve UserFinish:=True
ActiveCell.Offset(0, 1).Select
Else
erhand:
Resume Next
ActiveCell.Offset(0, 1).Select
End If
Loop
'Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub Opter4()
'Application.ScreenUpdating = False
'Call Module1.Iterations
Do While IsEmpty(ActiveCell.Offset(-13, 0)) = False
Call selecter3
Set changev = Selection
ActiveCell.Offset(57, 0).Select
SolverReset
SolverOptions AssumeNonNeg:=True, Derivatives:=2
SolverOk SetCell:=ActiveCell.Address, _
MaxMinVal:=2, _
ByChange:=kvar
SolverAdd CellRef:=kvar, Relation:=1, FormulaText:=Range(kvar).Offset(-1, 0).Value
SolverAdd CellRef:=kvar, Relation:=3, FormulaText:=Range(kvar).Offset(-2, 0).Value
SolverSolve UserFinish:=True
ActiveCell.Offset(0, 1).Select
'erhand:
'Resume Next
'ActiveCell.Offset(0, 1).Select
Loop
'Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub selecter()
'Application.ScreenUpdating = False
ActiveCell.Offset(SelNum, 0).Select
kvar = ActiveCell.Address
ActiveCell.Offset(1, 0).Select
vVar = ActiveCell.Address
ActiveCell.Offset(-1, 0).Select
ActiveCell.Resize(2).Select
End Sub
Sub selecter3()
'Application.ScreenUpdating = False
ActiveCell.Offset(-57, 0).Select
kvar = ActiveCell.Address
End Sub
Sub selecterMini()
ActiveCell.Offset(-3, 0).Select
kvar = ActiveCell.Address
ActiveCell.Offset(1, 0).Select
vVar = ActiveCell.Address
ActiveCell.Offset(-1, 0).Select
ActiveCell.Resize(2).Select
End Sub

Sub it2()
'Range("B9:J10").value = 0.5
'MsgBox kvar & " " & vVar
'MsgBox Range(kvar).Offset(-2, 0).value

Application.Run "SolverReset"
Application.Run "SolverOk", "ActiveCell.Address", 3, "0", "changev.Address"
Application.Run "SolverAdd", "kvar", 1, "Range(kvar).Offset(-1, 0).value"
Application.Run "SolverAdd", "kvar", 3, "Range(kvar).Offset(-2, 0).value"
Application.Run "SolverAdd", "vvar", 1, "Range(vVar).Offset(-4, 0).value"
Application.Run "SolverAdd", "vvar", 3, "Range(vVar).Offset(-5, 0).value"
Application.Run "SolverSolve", True
End Sub

 

[Mosen87]

I think by adding the following lines on Sub Opter, I can get better solutions, however it shows them as red in the code, probably a syntax error:
SolverOk SetCell:=ActiveCell.Address, _
MaxMinVal:=2, _
SolverAdd CellRef:=ActievCell, Relation:=1, FormulaText:=1 _
SolverAdd CellRef:=ActievCell, Relation:=3, FormulaText:=0.0 _
Simply, I wanna say, set the target function between 0 and 1.
Thanks

 

[Mosen87]

I modified the above codes to the below and this time I can run it. Look the last two constraints please (in Sub Opter):
SolverOptions AssumeNonNeg:=False, Derivatives:=2
SolverOk SetCell:=ActiveCell.Address, MaxMinVal:=2, ByChange:=changev.Address
SolverAdd CellRef:=kvar, Relation:=1, FormulaText:=Range(kvar).Offset(-1, 0).Value
SolverAdd CellRef:=kvar, Relation:=3, FormulaText:=Range(kvar).Offset(-2, 0).Value
SolverAdd CellRef:=vVar, Relation:=1, FormulaText:=Range(vVar).Offset(-4, 0).Value
SolverAdd CellRef:=vVar, Relation:=3, FormulaText:=Range(vVar).Offset(-5, 0).Value
SolverAdd CellRef:=ActievCell, Relation:=1, FormulaText:=1
SolverAdd CellRef:=ActievCell, Relation:=3, FormulaText:=0

But still for some columns, the target function is still a big value. I appreciate if you could help.

 

[joeu2004]

Please see the file I attach here.

The "attached file" is a screenshot. Contrary to the proverb, "a picture is __not__ worth 1000 (KB) words", at least not in this case.

In this forum, you should use the XL2BB add-in to copy-and-paste relevant parts of the Excel worksheet.

But you would need to provide __all__ dependent cells. If that is "too much", upload an example Excel file that demonstrates the problem to a file-sharing website, and post the public download URL in a response here.

(Some participants object to downloading Excel files. So try your best to use XL2BB. But some problems "require" the Excel file itself.)

Changing Max Iterations does not work because it has nothing to do with Solver.

In Solver, you can change Solver options to improve the accuracy of the result. In Solver, click Options.

Typically, I change the default Constraint Precision to 0.000000000001 in the All Methods tab. And in the GRG Nonlinear tab, I change the default convergence to 0.000000000001. Those are arbitrary choices; I simply insert enough zeros until the 1 is rightmost in the field.

(In the All Methods tab, you will also a field for Iterations under Solving Limits. I have __never__ used that. I do __not__ recommend changing it, at least not for the GRG Nonlinear method.)

But bear in mind that for some system of formulas, Solver will never find an "exact" solution. Sometimes, it helps to run Solver a second time; it seems to use the first solution as "guess". And sometimes, I will improve the solution manually; but that is only for posting purposes (overkill for most purposes).

Also, Solver usually requires "continuity" in the objective formula. Usually, it does not work well if the objective formula or formulas that it depends on have "discontinuities", such as explicit rounding, MIN/MAX/IF expressions, etc. (But sometimes we get lucky, and it works well enough.)

 

[Mosen87]

The "attached file" is a screenshot. Contrary to the proverb, "a picture is __not__ worth 1000 (KB) words", at least not in this case.

In this forum, you should use the XL2BB add-in to copy-and-paste relevant parts of the Excel worksheet.

But you would need to provide __all__ dependent cells. If that is "too much", upload an example Excel file that demonstrates the problem to a file-sharing website, and post the public download URL in a response here.

(Some participants object to downloading Excel files. So try your best to use XL2BB. But some problems "require" the Excel file itself.)

Changing Max Iterations does not work because it has nothing to do with Solver.

In Solver, you can change Solver options to improve the accuracy of the result. In Solver, click Options.

Typically, I change the default Constraint Precision to 0.000000000001 in the All Methods tab. And in the GRG Nonlinear tab, I change the default convergence to 0.000000000001. Those are arbitrary choices; I simply insert enough zeros until the 1 is rightmost in the field.

(In the All Methods tab, you will also a field for Iterations under Solving Limits. I have __never__ used that. I do __not__ recommend changing it, at least not for the GRG Nonlinear method.)

But bear in mind that for some system of formulas, Solver will never find an "exact" solution. Sometimes, it helps to run Solver a second time; it seems to use the first solution as "guess". And sometimes, I will improve the solution manually; but that is only for posting purposes (overkill for most purposes).

Also, Solver usually requires "continuity" in the objective formula. Usually, it does not work well if the objective formula or formulas that it depends on have "discontinuities", such as explicit rounding, MIN/MAX/IF expressions, etc. (But sometimes we get lucky, and it works well enough.)

Thanks, now the talk is on the macro file I attached, in particular in Sub Opter if you could help on that!

 

[Eric W]

First, that macro is a bit bigger than I have time to digest at the moment. However, I have one thing you can try. You have this line:

SolverOk SetCell:=ActiveCell.Address, MaxMinVal:=2, ByChange:=changev.Address

You can change that to

SolverOk SetCell:=ActiveCell.Address, MaxMinVal:=2, ByChange:=changev.Address, Engine:=3

and change the Engine value from 1-3. The 3 different engines have different ways of Solving, one of them might work better for your situation. Similarly, you can manually run the Solver on your worksheet several times, changing the parameters to try different scenarios. When you find something that works for you, use the Macro recorder to see how to code it in your macro. Good luck.

 

[stevendeg]

The max trails is not 100. Atleast not with 200 variables