Solver come out a different solversolve msg

[Lone]

I used macro to run the solver, but in some conditions, it come out a
different solversolve msg for the first time.

eg. when i run the first time, the solversolve msg come out "7", which means
"The conditions for Assume Linear Model are not satisfied", but the result is
correct. Once I repeat the solver, it returns 0. I have tried a few times,
but it come out same result.

Since I use the solversolve result to give a msg to user the result is ok or
not. Hope anyone can help.

//
SolverOptions MaxTime:=500, Iterations:=1000, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=True



SolverOk SetCell:="$F$6", MaxMinVal:=1, ValueOf:="0",
ByChange:=Range(Cells(8, 12), Cells(8, Icol))
result = SolverSolve(True)
//

 

[Dana DeLouis]

Hi. I'm not too sure of the question.
My experience is that Solver is usually pretty good at determining if
your model is Linear. Did you want to set AssumeLinear to False?
If you think your model is Linear, and you want to keep it that way,
there is a chance your model is poorly scaled.
Try setting Scaling:=True

= = =
HTH
Dana DeLouis

 

[Lone]

Thanks for your answer first. I have tried to set the scaling = True, but the
result is the same.

Maybe I can give you more background about my question.

Target Cell = A1,
A1: Text (A2, "0.0")
A2: = sumproduct (x1:x2, Y1:Y2)

I am not sure will it affrect the result, but it works fine for other case.

When I run the program 1st time:
It comes out "The conditions for Assume Linear Model are not satisfied."
But the result is almost correct, some result is almost a integer, but not
exactly.
example: 22835.0000005329

But I use solver to find the solution again. The result will 100% correct.
example:22835

Hope can give you more cleanly idea for my problem.

 

[Dana DeLouis]

Hi. I am going to guess at a few ideas.

Target Cell = A1,
A1: Text (A2, "0.0")

It appears your Target cell returns a text value that is rounded to 1
decimal place. It is "usually" best not to do this (using text for the
Target). You are basically rounding the result to 1 decimal place.
Solver gets confused when you round. It can't track why the answer jumps.

For example, if solver internally calculates 22835.001, or 22835.007, or
22835.000001, the Target answer is the same (22835.0)
Solver is trying to Change the input, but the output is the same.
Solver uses a technique known as "Finite Difference" to determine a
derivative. By rounding to 1 decimal place, Solver thinks your model is
"Non-Linear" Usually, Solver will error with this, so I'm surprised you
got an answer as you did.

It appears you want an integer solution. One way...
If X1:X2, and Y1:Y2 are the changing cells, add the constraint that
these cell are "Int" or Integer cells. Perhaps also lower the Tolerance
in the options area to a value closer to 0.

= = =
HTH :>)
Dana DeLouis

 

[Lone]

Thanks for your quick reply. I have change the Target Cells, but it's not work.
Below is my code for your reference.
Have 4 constraints, and force the simulation result must be integer (But it
not works for the 1st time)

'============================Set
Constraint=======================================


'Col J must greater than 0
SolverAdd CellRef:=Range(Cells(9, 10), Cells(Irow, 10)), Relation:=3,
FormulaText:="0"

'Simulation Result must less than demand
SolverAdd CellRef:=Range(Cells(8, 12), Cells(8, Icol)), Relation:=1,
FormulaText:=Range(Cells(2, 12), Cells(2, Icol))

'Result must greater than the Must Do Kit
SolverAdd CellRef:=Range(Cells(8, 12), Cells(8, Icol)), Relation:=3,
FormulaText:=Range(Cells(6, 12), Cells(6, Icol))

'Result must be a integer
SolverOk SetCell:="$F$6", MaxMinVal:=1, ValueOf:="0",
ByChange:=Range(Cells(8, 12), Cells(8, Icol))
SolverAdd CellRef:=Range(Cells(8, 12), Cells(8, Icol)), Relation:=4,
FormulaText:="integer"


'===========================Set Target &
Range=================================
SolverOptions MaxTime:=500, Iterations:=1000, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=0, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=True



SolverOk SetCell:="$F$6", MaxMinVal:=1, ValueOf:="0",
ByChange:=Range(Cells(8, 12), Cells(8, Icol))
result = SolverSolve(True)

===============================================================

 

[Dana DeLouis]

Hi. Notice that you have two SolverOk's in the code. Solver is running
"before" you have set up all your constraints and options.
As a technique, I would use Range Names for Ranges in your model.
I find that It makes it easier to spot any reference errors.
Here is your code without the first SolverOk...

Option Explicit

' ==Set Constraint===

'// Let's start clean...
SolverReset

'Col J must greater than 0
SolverAdd CellRef:=Range(Cells(9, 10), Cells(Irow, 10)), _
Relation:=3, _
FormulaText:="0"

'Simulation Result must less than demand
SolverAdd CellRef:=Range(Cells(8, 12), Cells(8, Icol)), _
Relation:=1, _
FormulaText:=Range(Cells(2, 12), Cells(2, Icol))

'Result must greater than the Must Do Kit
SolverAdd CellRef:=Range(Cells(8, 12), Cells(8, Icol)), _
Relation:=3, _
FormulaText:=Range(Cells(6, 12), Cells(6, Icol))

'Result must be a integer
SolverAdd CellRef:=Range(Cells(8, 12), Cells(8, Icol)), _
Relation:=4, _
FormulaText:="integer"


'// Options
SolverOptions _
MaxTime:=500, _
Iterations:=1000, _
Precision:=0.000001, _
AssumeLinear:=True, _
StepThru:=False, _
Estimates:=1, _
Derivatives:=1, _
SearchOption:=1, _
IntTolerance:=0, _
Scaling:=False, _
Convergence:=0.0001, _
AssumeNonNeg:=True


'// You can drop "Value" in a Max Problem...
SolverOk _
SetCell:="F6", _
MaxMinVal:=1, _
ByChange:=Range(Cells(8, 12), Cells(8, Icol))

result = SolverSolve(True)

'= = =
HTH :>)
Dana DeLouis

 

[Dana DeLouis]

Hi. This is not necessary, but only a technique.
There are better ways, but sse if any techniques here are helpful.
This is just a simple demo.


Sub Demo()
Dim Result

With ActiveWorkbook.Names
.Add "Target", [A8]
.Add "Chg", [A1:A5]
.Add "UpperLimit", [B1:B5]
End With

SolverReset

SolverAdd [Chg], 3, 0
SolverAdd [Chg], 1, [UpperLimit]
SolverAdd [Chg], 4, "integer"

SolverOk [Target], 1, , [Chg]

Result = SolverSolve(True)
End Sub



The idea is that when one has
CellRef:=Range(Cells(8, 12), Cells(8, Icol))
listed throughout the code, it increases the odds of one of them having
a typo It makes it hard to spot errors.


Anyway, hope this helps.
Dana DeLouis

 

[Lone]

Thanks for your advise.

I replace my code but it still have same problem. I found some information
from the web and would like to discuss.

Below FAQ is similar to my problem, I can see fractional values after solver
run, will the program occur error casued by the fractional values? If
possible, I would like to share my template to you.

================================================
Q. I specified that certain variables in my model have to be integers. But
when the Solver runs, I see fractional values in the variable cells. Is there
something wrong?

A. This is normal. The Solver uses the Branch & Bound method to deal with
integer variables. This means that during the optimization, the Solver will
indeed use fractional values, but when the optimal solution is found, all
integer variables should have integer values.
==================================================