Solver makes a 'small' change in a variable to see the effect on the=20
objective function (Set cell). It is the standard way to find if a=20
variable should be increased or decreased. If there is no change,=20
Solver decides that the objective function cannot be improved.

This typically happens for non-linear functions that have hit what is=20
called a 'local optimum.' Consider a simple problem. The 'set cell'=20
condition is set to 'maximum' and has the formula =3DMax(A1,B1). The 'by=
=20
changing cells are A1:B1. Add the constraint B1<=3D10. Now, put 10 in=20
B1 and 1 in A1. Run Solver. Even though we know that A1 can be made=20
arbitrarily large, Solver will never find that solution. Solver will=20
make a small change in A1 and notice that it has no effect on the=20
objective. Hence, it will conclude that there's nothing it can do to=20
improve the objective.

This is not a problem with Solver but a limitation of the nature of the=20
problem, which is a non-linear optimization. =20

--=20
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,=20
says...
More information: I just found some information in the help system statin=
g=20
"When the relative change in the target cell value is less than the numbe=
r in=20
the Convergence box for the last five iterations, Solver stops."
This seems to be what's happening. By selecting "Show Iteration Results" =
in=20
the Options window I can see the five iterations. And nothing is changing=
=20
from one attempt to the next. I'm even looking at the numbers out 25 plac=
es.
=20
Why isn't at least one variable changed?
=20
Rick
=20
"Rick" wrote:
=20
Tushar,
=20
Well, I've tried several versions of this model without success.
First of all, maybe I didn't understand your reference to linear=20
optimization. Were you telling me to select "Assume Linear Model" in th=
e=20
Options window? That didn't seem to make a difference.
=20
Is there any way I can send you the worksheet I'm working with to show =
you=20
all of the constraints and my latest attempt?
=20
The solver only makes a half dozen trials which all seem to be the same=
and=20
returns a solution that does not fit the constraints. And, it doesn't m=
atter=20
what values I start with. Nothing changes between the initial setpoints=
and=20
those returned by the solver.
=20
Rick
=20
"Rick" wrote:
=20
Thanks. I'll give this a try. I didn't realize I could add constraint=
s to a=20
cell containing a formula. It will be interesting as there are four g=
ears in=20
the system with several levers allowing additional multipliers for th=
e final=20
ratio. I also have to be sure the same gear isn't selected for each p=
osition=20
as I only have one of each gear.
=20
What are my limits on constraints?
=20
"Tushar Mehta" wrote:
=20
Assuming you have created a linear optimization (much faster than a=
=20
non-linear problem and guaranteed to give you a global solution), y=
ou=20
can maintain the linear nature of the problem with the following=20
approach:
=20
Suppose you have the gears (each with an integer number of teeth) i=
n=20
column A. For my test I used A1:A6, which had the values 1,2,3,5,8=
,=20
and 13.
=20
Then, designate a corresponding range in some other column as the=
=20
'selector' range. I picked C1:C6. In C7 enter the formula =3DSUM
(C1:C6). Designate some other cell as the 'selected gear' cell. I=
=20
picked D1. D1 should contain the formula =3DSUMPRODUCT(A1:A6,C1:C6=
)
=20
In Solver, add the contraints: C7<=3D1 and C1:C6 are binary.
=20
The way the above works is as follows: Because of C7, only one of=
=20
C1:C6 can be 1 (the others will be zero). Then, the SUMPRODUCT wil=
l=20
multiply the selected gear by 1 and all others by zero. The result=
ing=20
'sum' will be the selected gear.
=20
--=20
Regards,
=20
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
=20
In article ,=20
says...
I=C3=A2=E2=3D3F=AC=3D3Fm trying to use Solver to determine gears =
in a gear train to achieve a=20
specified ratio input to output. My problem is I don=C3=A2=E2=3D3=
F=AC=3D3Ft have a continuous=20
range of gears. Of course, the gears have to have an integer as t=
he number of=20
teeth, but how do I set the constraint to be a listing of the ava=
ilable gears?
=20
I=C3=A2=E2=3D3F=AC=3D3Fve been all over the help system furnished=
with Excel and find notes about=20
setting the constraint as a range reference, but I get an error m=
essage when=20
I try it. =C3=A2=E2=3D3F=AC=C5=3D3FUnequal number of cells in Cel=
l Reference and Constraint.=C3=A2=E2=3D3F=AC=C2=9D
=20
Any ideas?
=20
=20
=20
=20