Solver Constraints

I’m trying to use Solver to determine gears in a gear train to achieve a
specified ratio input to output. My problem is I don’t have a continuous
range of gears. Of course, the gears have to have an integer as the number of
teeth, but how do I set the constraint to be a listing of the available gears?

I’ve been all over the help system furnished with Excel and find notes about
setting the constraint as a range reference, but I get an error message when
I try it. “Unequal number of cells in Cell Reference and Constraint.�

Any ideas?

Assuming you have created a linear optimization (much faster than a=20
non-linear problem and guaranteed to give you a global solution), you=20
can maintain the linear nature of the problem with the following=20
approach:

Suppose you have the gears (each with an integer number of teeth) in=20
column A. For my test I used A1:A6, which had the values 1,2,3,5,8,=20
and 13.

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)

In Solver, add the contraints: C7<=3D1 and C1:C6 are binary.

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 will=20
multiply the selected gear by 1 and all others by zero. The resulting=20
'sum' will be the selected gear.

--=20
Regards,

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

In article <[email protected]>,=20
[email protected] says...
> I=E2=A4=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=E2=A4=3D3Ft have a c=
ontinuous=20
> range of gears. Of course, the gears have to have an integer as the numbe=
r of=20
> teeth, but how do I set the constraint to be a listing of the available g=
ears?
>=20
> I=E2=A4=3D3Fve been all over the help system furnished with Excel and fin=
d notes about=20
> setting the constraint as a range reference, but I get an error message w=
hen=20
> I try it. =E2=A4=BDUnequal number of cells in Cell Reference and Constrai=
nt.=E2=A4=9D
>=20
> Any ideas?
>=20
>=20

Thanks. I'll give this a try. I didn't realize I could add constraints to a
cell containing a formula. It will be interesting as there are four gears in
the system with several levers allowing additional multipliers for the final
ratio. I also have to be sure the same gear isn't selected for each position
as I only have one of each gear.

What are my limits on constraints?

"Tushar Mehta" wrote:

> Assuming you have created a linear optimization (much faster than a
> non-linear problem and guaranteed to give you a global solution), you
> can maintain the linear nature of the problem with the following
> approach:
>
> Suppose you have the gears (each with an integer number of teeth) in
> column A. For my test I used A1:A6, which had the values 1,2,3,5,8,
> and 13.
>
> Then, designate a corresponding range in some other column as the
> 'selector' range. I picked C1:C6. In C7 enter the formula =SUM
> (C1:C6). Designate some other cell as the 'selected gear' cell. I
> picked D1. D1 should contain the formula =SUMPRODUCT(A1:A6,C1:C6)
>
> In Solver, add the contraints: C7<=1 and C1:C6 are binary.
>
> The way the above works is as follows: Because of C7, only one of
> C1:C6 can be 1 (the others will be zero). Then, the SUMPRODUCT will
> multiply the selected gear by 1 and all others by zero. The resulting
> 'sum' will be the selected gear.
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> In article <[email protected]>,
> [email protected] says...
> > Iâ€=3Fm trying to use Solver to determine gears in a gear train to achieve a
> > specified ratio input to output. My problem is I donâ€=3Ft have a continuous
> > range of gears. Of course, the gears have to have an integer as the number of
> > teeth, but how do I set the constraint to be a listing of the available gears?
> >
> > Iâ€=3Fve been all over the help system furnished with Excel and find notes about
> > setting the constraint as a range reference, but I get an error message when
> > I try it. “Unequal number of cells in Cell Reference and Constraint.�
> >
> > Any ideas?
> >
> >
>

Tushar,

Well, I've tried several versions of this model without success.
First of all, maybe I didn't understand your reference to linear
optimization. Were you telling me to select "Assume Linear Model" in the
Options window? That didn't seem to make a difference.

Is there any way I can send you the worksheet I'm working with to show you
all of the constraints and my latest attempt?

The solver only makes a half dozen trials which all seem to be the same and
returns a solution that does not fit the constraints. And, it doesn't matter
what values I start with. Nothing changes between the initial setpoints and
those returned by the solver.

Rick

"Rick" wrote:

> Thanks. I'll give this a try. I didn't realize I could add constraints to a
> cell containing a formula. It will be interesting as there are four gears in
> the system with several levers allowing additional multipliers for the final
> ratio. I also have to be sure the same gear isn't selected for each position
> as I only have one of each gear.
>
> What are my limits on constraints?
>
> "Tushar Mehta" wrote:
>
> > Assuming you have created a linear optimization (much faster than a
> > non-linear problem and guaranteed to give you a global solution), you
> > can maintain the linear nature of the problem with the following
> > approach:
> >
> > Suppose you have the gears (each with an integer number of teeth) in
> > column A. For my test I used A1:A6, which had the values 1,2,3,5,8,
> > and 13.
> >
> > Then, designate a corresponding range in some other column as the
> > 'selector' range. I picked C1:C6. In C7 enter the formula =SUM
> > (C1:C6). Designate some other cell as the 'selected gear' cell. I
> > picked D1. D1 should contain the formula =SUMPRODUCT(A1:A6,C1:C6)
> >
> > In Solver, add the contraints: C7<=1 and C1:C6 are binary.
> >
> > The way the above works is as follows: Because of C7, only one of
> > C1:C6 can be 1 (the others will be zero). Then, the SUMPRODUCT will
> > multiply the selected gear by 1 and all others by zero. The resulting
> > 'sum' will be the selected gear.
> >
> > --
> > Regards,
> >
> > Tushar Mehta
> > www.tushar-mehta.com
> > Excel, PowerPoint, and VBA add-ins, tutorials
> > Custom MS Office productivity solutions
> >
> > In article <[email protected]>,
> > [email protected] says...
> > > Iâ€=3Fm trying to use Solver to determine gears in a gear train to achieve a
> > > specified ratio input to output. My problem is I donâ€=3Ft have a continuous
> > > range of gears. Of course, the gears have to have an integer as the number of
> > > teeth, but how do I set the constraint to be a listing of the available gears?
> > >
> > > Iâ€=3Fve been all over the help system furnished with Excel and find notes about
> > > setting the constraint as a range reference, but I get an error message when
> > > I try it. “Unequal number of cells in Cell Reference and Constraint.�
> > >
> > > Any ideas?
> > >
> > >
> >

More information: I just found some information in the help system stating
"When the relative change in the target cell value is less than the number in
the Convergence box for the last five iterations, Solver stops."
This seems to be what's happening. By selecting "Show Iteration Results" in
the Options window I can see the five iterations. And nothing is changing
from one attempt to the next. I'm even looking at the numbers out 25 places.

Why isn't at least one variable changed?

Rick

"Rick" wrote:

> Tushar,
>
> Well, I've tried several versions of this model without success.
> First of all, maybe I didn't understand your reference to linear
> optimization. Were you telling me to select "Assume Linear Model" in the
> Options window? That didn't seem to make a difference.
>
> Is there any way I can send you the worksheet I'm working with to show you
> all of the constraints and my latest attempt?
>
> The solver only makes a half dozen trials which all seem to be the same and
> returns a solution that does not fit the constraints. And, it doesn't matter
> what values I start with. Nothing changes between the initial setpoints and
> those returned by the solver.
>
> Rick
>
> "Rick" wrote:
>
> > Thanks. I'll give this a try. I didn't realize I could add constraints to a
> > cell containing a formula. It will be interesting as there are four gears in
> > the system with several levers allowing additional multipliers for the final
> > ratio. I also have to be sure the same gear isn't selected for each position
> > as I only have one of each gear.
> >
> > What are my limits on constraints?
> >
> > "Tushar Mehta" wrote:
> >
> > > Assuming you have created a linear optimization (much faster than a
> > > non-linear problem and guaranteed to give you a global solution), you
> > > can maintain the linear nature of the problem with the following
> > > approach:
> > >
> > > Suppose you have the gears (each with an integer number of teeth) in
> > > column A. For my test I used A1:A6, which had the values 1,2,3,5,8,
> > > and 13.
> > >
> > > Then, designate a corresponding range in some other column as the
> > > 'selector' range. I picked C1:C6. In C7 enter the formula =SUM
> > > (C1:C6). Designate some other cell as the 'selected gear' cell. I
> > > picked D1. D1 should contain the formula =SUMPRODUCT(A1:A6,C1:C6)
> > >
> > > In Solver, add the contraints: C7<=1 and C1:C6 are binary.
> > >
> > > The way the above works is as follows: Because of C7, only one of
> > > C1:C6 can be 1 (the others will be zero). Then, the SUMPRODUCT will
> > > multiply the selected gear by 1 and all others by zero. The resulting
> > > 'sum' will be the selected gear.
> > >
> > > --
> > > Regards,
> > >
> > > Tushar Mehta
> > > www.tushar-mehta.com
> > > Excel, PowerPoint, and VBA add-ins, tutorials
> > > Custom MS Office productivity solutions
> > >
> > > In article <[email protected]>,
> > > [email protected] says...
> > > > Iâ€=3Fm trying to use Solver to determine gears in a gear train to achieve a
> > > > specified ratio input to output. My problem is I donâ€=3Ft have a continuous
> > > > range of gears. Of course, the gears have to have an integer as the number of
> > > > teeth, but how do I set the constraint to be a listing of the available gears?
> > > >
> > > > Iâ€=3Fve been all over the help system furnished with Excel and find notes about
> > > > setting the constraint as a range reference, but I get an error message when
> > > > I try it. “Unequal number of cells in Cell Reference and Constraint.�
> > > >
> > > > Any ideas?
> > > >
> > > >
> > >

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 <[email protected]>,=20
[email protected] 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 <[email protected]>,=20
> > > > [email protected] 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

You are welcome to send me the workbook as long as we agree on two=20
things.

(1) When I get to work on it will depend on how easy it is to=20
understand what is going on. If the XL/Solver model is 'obvious' I=20
will need less time to figure out what is going on. If it is not, it=20
might be several days before I get to it.

(2) I am reluctant to open a workbook with macros and will not *under=20
any circumstances* open one which contains XLM macros.

--=20
Regards,

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

In article <[email protected]>,=20
[email protected] says...
> 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 the=
=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 yo=
u=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 a=
nd=20
> returns a solution that does not fit the constraints. And, it doesn't mat=
ter=20
> what values I start with. Nothing changes between the initial setpoints a=
nd=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 constraints =
to a=20
> > cell containing a formula. It will be interesting as there are four gea=
rs in=20
> > the system with several levers allowing additional multipliers for the =
final=20
> > ratio. I also have to be sure the same gear isn't selected for each pos=
ition=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), you=
=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) in=
=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 will=
=20
> > > multiply the selected gear by 1 and all others by zero. The resultin=
g=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 <[email protected]>,=20
> > > [email protected] 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=3D3F=
=AC=3D3Ft have a continuous=20
> > > > range of gears. Of course, the gears have to have an integer as the=
number of=20
> > > > teeth, but how do I set the constraint to be a listing of the avail=
able gears?
> > > >=20
> > > > I=C3=A2=E2=3D3F=AC=3D3Fve been all over the help system furnished w=
ith Excel and find notes about=20
> > > > setting the constraint as a range reference, but I get an error mes=
sage when=20
> > > > I try it. =C3=A2=E2=3D3F=AC=C5=3D3FUnequal number of cells in Cell =
Reference and Constraint.=C3=A2=E2=3D3F=AC=C2=9D
> > > >=20
> > > > Any ideas?
> > > >=20
> > > >=20
> > >=20
>=20

Just to add. You may want to change your initial starting values. By
starting from different numbers, Solver may find a different local minimum
(or maximum). Make sure your model doesn't use any IF statements, as this
is one common source of problems for Solver.

--
Dana DeLouis
Win XP & Office 2003


"Rick" <[email protected]> wrote in message
news:[email protected]...
> More information: I just found some information in the help system stating
> "When the relative change in the target cell value is less than the number
> in
> the Convergence box for the last five iterations, Solver stops."
> This seems to be what's happening. By selecting "Show Iteration Results"
> in
> the Options window I can see the five iterations. And nothing is changing
> from one attempt to the next. I'm even looking at the numbers out 25
> places.
>
> Why isn't at least one variable changed?
>
<snip>

I tried changing the system around based on the IF statement advice. No
improvement.
For some reason, Solver changes one variable (which happens to have a binary
constraint) and makes five attempts without changing anything else. It
doesn't even change this one back to zero.
Isn't a binary constraint really a form of an IF statement? If zero, than
something. If one, then something else. It is not linear.

"Dana DeLouis" wrote:

> Just to add. You may want to change your initial starting values. By
> starting from different numbers, Solver may find a different local minimum
> (or maximum). Make sure your model doesn't use any IF statements, as this
> is one common source of problems for Solver.
>
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "Rick" <[email protected]> wrote in message
> news:[email protected]...
> > More information: I just found some information in the help system stating
> > "When the relative change in the target cell value is less than the number
> > in
> > the Convergence box for the last five iterations, Solver stops."
> > This seems to be what's happening. By selecting "Show Iteration Results"
> > in
> > the Options window I can see the five iterations. And nothing is changing
> > from one attempt to the next. I'm even looking at the numbers out 25
> > places.
> >
> > Why isn't at least one variable changed?
> >
> <snip>
>
>
>

Sounds like you may be using other functions that "Jump." (ie Max, Min,
VLookUp, If ...) Feel free to send me your workbook. I'd be interested
in seeing a real world "gear ratio" problem. Thanks. :>)

--
Dana DeLouis
Win XP & Office 2003


"Rick" <[email protected]> wrote in message
news:[email protected]...
>I tried changing the system around based on the IF statement advice. No
> improvement.
> For some reason, Solver changes one variable (which happens to have a
> binary
> constraint) and makes five attempts without changing anything else. It
> doesn't even change this one back to zero.
> Isn't a binary constraint really a form of an IF statement? If zero, than
> something. If one, then something else. It is not linear.
>
> "Dana DeLouis" wrote:
>
>> Just to add. You may want to change your initial starting values. By
>> starting from different numbers, Solver may find a different local
>> minimum
>> (or maximum). Make sure your model doesn't use any IF statements, as
>> this
>> is one common source of problems for Solver.
>>
>> --
>> Dana DeLouis
>> Win XP & Office 2003
>>
>>
>> "Rick" <[email protected]> wrote in message
>> news:[email protected]...
>> > More information: I just found some information in the help system
>> > stating
>> > "When the relative change in the target cell value is less than the
>> > number
>> > in
>> > the Convergence box for the last five iterations, Solver stops."
>> > This seems to be what's happening. By selecting "Show Iteration
>> > Results"
>> > in
>> > the Options window I can see the five iterations. And nothing is
>> > changing
>> > from one attempt to the next. I'm even looking at the numbers out 25
>> > places.
>> >
>> > Why isn't at least one variable changed?
>> >
>> <snip>
>>
>>
>>

Hi Rick. Thanks for the wb. I'm a little slow at understanding it, but
here are my first impressions. It looks like Solver should be a good tool
to use, so you're on the right track. Looks good so far. We should get
this working...
The use of the "Round" function however, stands out like a main culprit.
Solver really doesn't like these types of functions.
Here's the reason. Suppose Solver is varying a cell from 4.1, then 4.2,
4.3., 4.4 ..etc, but the round function keeps this cell at 4. (Rounded
down). It was hoping that a small change would help it determine which way
to go next. If it tries 4.6, the output "Jumps" to 5. ?? Solver can't
figure out what happened??
Also, the Target cell is set to a value of 0. My thoughts...Solver is
looking for an "EXACT" value of zero, but this may not be likely with
integer constraints. You can get "close" to zero, but most likely you will
not reach zero. Solver gives up trying to get closer. Usually, an
alternative is to "Minimize" the target cell, with the added constraint that
the target is >=0. Or more likely Target >=-.005, or something like this.
I noticed in one part you are trying to choose between two ratio's. Binary
in one constraint, an If( ) function in another cell to pick the opposite
choice, and the use of Sumproduct. The use of If, as was mentioned, is
really a big no-no with Solver.
This is where it comes down to more of an "art" than a science. Your
choices that you are looking for are either 1, or 1/2. A Boolean constraint
is what you want. Have the Boolean value of "1" represent 1, and a "0"
represent 1/2. Suppose you Boolean value is in A1. Then the choice of
gears would be: =(A1+1)/2
Here, when A1 is 1, the output is 1. If 0, then the output is 1/2. This is
how Solver will choose between 1 or 1/2.
Another advantage this simple example has over SumProduct is that it uses
only 1 variable. Sumproduct would require two variables. You are very
close to the 200 changing cell limit of Solver, so every bit helps.
Anyway, hope this helps. I'll take a look at it some more. :>)

--
Dana DeLouis
Win XP & Office 2003


"Dana DeLouis" <[email protected]> wrote in message
news:[email protected]...
> Sounds like you may be using other functions that "Jump." (ie Max, Min,
> VLookUp, If ...) Feel free to send me your workbook. I'd be
> interested in seeing a real world "gear ratio" problem. Thanks. :>)
>
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "Rick" <[email protected]> wrote in message
> news:[email protected]...
>>I tried changing the system around based on the IF statement advice. No
>> improvement.
>> For some reason, Solver changes one variable (which happens to have a
>> binary
>> constraint) and makes five attempts without changing anything else. It
>> doesn't even change this one back to zero.
>> Isn't a binary constraint really a form of an IF statement? If zero, than
>> something. If one, then something else. It is not linear.
>>
>> "Dana DeLouis" wrote:
>>
>>> Just to add. You may want to change your initial starting values. By
>>> starting from different numbers, Solver may find a different local
>>> minimum
>>> (or maximum). Make sure your model doesn't use any IF statements, as
>>> this
>>> is one common source of problems for Solver.
>>>
>>> --
>>> Dana DeLouis
>>> Win XP & Office 2003
>>>
>>>
>>> "Rick" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > More information: I just found some information in the help system
>>> > stating
>>> > "When the relative change in the target cell value is less than the
>>> > number
>>> > in
>>> > the Convergence box for the last five iterations, Solver stops."
>>> > This seems to be what's happening. By selecting "Show Iteration
>>> > Results"
>>> > in
>>> > the Options window I can see the five iterations. And nothing is
>>> > changing
>>> > from one attempt to the next. I'm even looking at the numbers out 25
>>> > places.
>>> >
>>> > Why isn't at least one variable changed?
>>> >
>>> <snip>
>>>
>>>
>>>
>
>

Just for some additional ideas... Here is one of your cells:

=IF(E7=1,1,IF(E7=2,8/9,IF(E7=3,8/10,IF(E7=4,8/11,IF(E7=5,8/12,IF(E7=6,8/14,0))))))

One option would be to make E7 an "Integer" constraint, and add the
constraint that E7 vary from 1-6. Then, the same ratio's from above could
be calculated as:

960/(720+E7*(394+E7*(-225+E7*(85+(E7 - 15)*E7))))

This is just one way to remove IF statements. There's better equations that
could help "zero out" the derivative at each point, but this polynomial
should be good enough to start with.
HTH
--
Dana DeLouis
Win XP & Office 2003


"Dana DeLouis" <[email protected]> wrote in message
news:[email protected]...
> Hi Rick. Thanks for the wb. I'm a little slow at understanding it, but
> here are my first impressions. It looks like Solver should be a good tool
> to use, so you're on the right track. Looks good so far. We should get
> this working...
> The use of the "Round" function however, stands out like a main culprit.
> Solver really doesn't like these types of functions.
> Here's the reason. Suppose Solver is varying a cell from 4.1, then 4.2,
> 4.3., 4.4 ..etc, but the round function keeps this cell at 4. (Rounded
> down). It was hoping that a small change would help it determine which
> way to go next. If it tries 4.6, the output "Jumps" to 5. ?? Solver
> can't figure out what happened??
> Also, the Target cell is set to a value of 0. My thoughts...Solver is
> looking for an "EXACT" value of zero, but this may not be likely with
> integer constraints. You can get "close" to zero, but most likely you
> will not reach zero. Solver gives up trying to get closer. Usually, an
> alternative is to "Minimize" the target cell, with the added constraint
> that the target is >=0. Or more likely Target >=-.005, or something like
> this.
> I noticed in one part you are trying to choose between two ratio's.
> Binary in one constraint, an If( ) function in another cell to pick the
> opposite choice, and the use of Sumproduct. The use of If, as was
> mentioned, is really a big no-no with Solver.
> This is where it comes down to more of an "art" than a science. Your
> choices that you are looking for are either 1, or 1/2. A Boolean
> constraint is what you want. Have the Boolean value of "1" represent 1,
> and a "0" represent 1/2. Suppose you Boolean value is in A1. Then the
> choice of gears would be: =(A1+1)/2
> Here, when A1 is 1, the output is 1. If 0, then the output is 1/2. This
> is how Solver will choose between 1 or 1/2.
> Another advantage this simple example has over SumProduct is that it uses
> only 1 variable. Sumproduct would require two variables. You are very
> close to the 200 changing cell limit of Solver, so every bit helps.
> Anyway, hope this helps. I'll take a look at it some more. :>)
>
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "Dana DeLouis" <[email protected]> wrote in message
> news:[email protected]...
>> Sounds like you may be using other functions that "Jump." (ie Max, Min,
>> VLookUp, If ...) Feel free to send me your workbook. I'd be
>> interested in seeing a real world "gear ratio" problem. Thanks. :>)
>>
>> --
>> Dana DeLouis
>> Win XP & Office 2003
>>
>>
>> "Rick" <[email protected]> wrote in message
>> news:[email protected]...
>>>I tried changing the system around based on the IF statement advice. No
>>> improvement.
>>> For some reason, Solver changes one variable (which happens to have a
>>> binary
>>> constraint) and makes five attempts without changing anything else. It
>>> doesn't even change this one back to zero.
>>> Isn't a binary constraint really a form of an IF statement? If zero,
>>> than
>>> something. If one, then something else. It is not linear.
>>>
>>> "Dana DeLouis" wrote:
>>>
>>>> Just to add. You may want to change your initial starting values. By
>>>> starting from different numbers, Solver may find a different local
>>>> minimum
>>>> (or maximum). Make sure your model doesn't use any IF statements, as
>>>> this
>>>> is one common source of problems for Solver.
>>>>
>>>> --
>>>> Dana DeLouis
>>>> Win XP & Office 2003
>>>>
>>>>
>>>> "Rick" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>> > More information: I just found some information in the help system
>>>> > stating
>>>> > "When the relative change in the target cell value is less than the
>>>> > number
>>>> > in
>>>> > the Convergence box for the last five iterations, Solver stops."
>>>> > This seems to be what's happening. By selecting "Show Iteration
>>>> > Results"
>>>> > in
>>>> > the Options window I can see the five iterations. And nothing is
>>>> > changing
>>>> > from one attempt to the next. I'm even looking at the numbers out 25
>>>> > places.
>>>> >
>>>> > Why isn't at least one variable changed?
>>>> >
>>>> <snip>
>>>>
>>>>
>>>>
>>
>>
>
>

Just a comment: the "Round" function was put in because the binary constraint
doesn't seem to really work. Values such as .999998 or .00001 are used. I've
even seen values as far off as .87. These values don't work well in this
situation.
Why doesn't the binary constraint use values of 0 or 1 only?

Rick

"Dana DeLouis" wrote:

> Just for some additional ideas... Here is one of your cells:
>
> =IF(E7=1,1,IF(E7=2,8/9,IF(E7=3,8/10,IF(E7=4,8/11,IF(E7=5,8/12,IF(E7=6,8/14,0))))))
>
> One option would be to make E7 an "Integer" constraint, and add the
> constraint that E7 vary from 1-6. Then, the same ratio's from above could
> be calculated as:
>
> 960/(720+E7*(394+E7*(-225+E7*(85+(E7 - 15)*E7))))
>
> This is just one way to remove IF statements. There's better equations that
> could help "zero out" the derivative at each point, but this polynomial
> should be good enough to start with.
> HTH
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "Dana DeLouis" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Rick. Thanks for the wb. I'm a little slow at understanding it, but
> > here are my first impressions. It looks like Solver should be a good tool
> > to use, so you're on the right track. Looks good so far. We should get
> > this working...
> > The use of the "Round" function however, stands out like a main culprit.
> > Solver really doesn't like these types of functions.
> > Here's the reason. Suppose Solver is varying a cell from 4.1, then 4.2,
> > 4.3., 4.4 ..etc, but the round function keeps this cell at 4. (Rounded
> > down). It was hoping that a small change would help it determine which
> > way to go next. If it tries 4.6, the output "Jumps" to 5. ?? Solver
> > can't figure out what happened??
> > Also, the Target cell is set to a value of 0. My thoughts...Solver is
> > looking for an "EXACT" value of zero, but this may not be likely with
> > integer constraints. You can get "close" to zero, but most likely you
> > will not reach zero. Solver gives up trying to get closer. Usually, an
> > alternative is to "Minimize" the target cell, with the added constraint
> > that the target is >=0. Or more likely Target >=-.005, or something like
> > this.
> > I noticed in one part you are trying to choose between two ratio's.
> > Binary in one constraint, an If( ) function in another cell to pick the
> > opposite choice, and the use of Sumproduct. The use of If, as was
> > mentioned, is really a big no-no with Solver.
> > This is where it comes down to more of an "art" than a science. Your
> > choices that you are looking for are either 1, or 1/2. A Boolean
> > constraint is what you want. Have the Boolean value of "1" represent 1,
> > and a "0" represent 1/2. Suppose you Boolean value is in A1. Then the
> > choice of gears would be: =(A1+1)/2
> > Here, when A1 is 1, the output is 1. If 0, then the output is 1/2. This
> > is how Solver will choose between 1 or 1/2.
> > Another advantage this simple example has over SumProduct is that it uses
> > only 1 variable. Sumproduct would require two variables. You are very
> > close to the 200 changing cell limit of Solver, so every bit helps.
> > Anyway, hope this helps. I'll take a look at it some more. :>)
> >
> > --
> > Dana DeLouis
> > Win XP & Office 2003
> >
> >
> > "Dana DeLouis" <[email protected]> wrote in message
> > news:[email protected]...
> >> Sounds like you may be using other functions that "Jump." (ie Max, Min,
> >> VLookUp, If ...) Feel free to send me your workbook. I'd be
> >> interested in seeing a real world "gear ratio" problem. Thanks. :>)
> >>
> >> --
> >> Dana DeLouis
> >> Win XP & Office 2003
> >>
> >>
> >> "Rick" <[email protected]> wrote in message
> >> news:[email protected]...
> >>>I tried changing the system around based on the IF statement advice. No
> >>> improvement.
> >>> For some reason, Solver changes one variable (which happens to have a
> >>> binary
> >>> constraint) and makes five attempts without changing anything else. It
> >>> doesn't even change this one back to zero.
> >>> Isn't a binary constraint really a form of an IF statement? If zero,
> >>> than
> >>> something. If one, then something else. It is not linear.
> >>>
> >>> "Dana DeLouis" wrote:
> >>>
> >>>> Just to add. You may want to change your initial starting values. By
> >>>> starting from different numbers, Solver may find a different local
> >>>> minimum
> >>>> (or maximum). Make sure your model doesn't use any IF statements, as
> >>>> this
> >>>> is one common source of problems for Solver.
> >>>>
> >>>> --
> >>>> Dana DeLouis
> >>>> Win XP & Office 2003
> >>>>
> >>>>
> >>>> "Rick" <[email protected]> wrote in message
> >>>> news:[email protected]...
> >>>> > More information: I just found some information in the help system
> >>>> > stating
> >>>> > "When the relative change in the target cell value is less than the
> >>>> > number
> >>>> > in
> >>>> > the Convergence box for the last five iterations, Solver stops."
> >>>> > This seems to be what's happening. By selecting "Show Iteration
> >>>> > Results"
> >>>> > in
> >>>> > the Options window I can see the five iterations. And nothing is
> >>>> > changing
> >>>> > from one attempt to the next. I'm even looking at the numbers out 25
> >>>> > places.
> >>>> >
> >>>> > Why isn't at least one variable changed?
> >>>> >
> >>>> <snip>
> >>>>
> >>>>
> >>>>
> >>
> >>
> >
> >
>
>
>

There's a similar problem with the integer constraint. If I set the
constraints on the value of a cell to be >=0, <=1, & int, I will still see
values other than 0 and 1. Why?

"Rick" wrote:

> Just a comment: the "Round" function was put in because the binary constraint
> doesn't seem to really work. Values such as .999998 or .00001 are used. I've
> even seen values as far off as .87. These values don't work well in this
> situation.
> Why doesn't the binary constraint use values of 0 or 1 only?
>
> Rick
>
> "Dana DeLouis" wrote:
>
> > Just for some additional ideas... Here is one of your cells:
> >
> > =IF(E7=1,1,IF(E7=2,8/9,IF(E7=3,8/10,IF(E7=4,8/11,IF(E7=5,8/12,IF(E7=6,8/14,0))))))
> >
> > One option would be to make E7 an "Integer" constraint, and add the
> > constraint that E7 vary from 1-6. Then, the same ratio's from above could
> > be calculated as:
> >
> > 960/(720+E7*(394+E7*(-225+E7*(85+(E7 - 15)*E7))))
> >
> > This is just one way to remove IF statements. There's better equations that
> > could help "zero out" the derivative at each point, but this polynomial
> > should be good enough to start with.
> > HTH
> > --
> > Dana DeLouis
> > Win XP & Office 2003
> >
> >
> > "Dana DeLouis" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Rick. Thanks for the wb. I'm a little slow at understanding it, but
> > > here are my first impressions. It looks like Solver should be a good tool
> > > to use, so you're on the right track. Looks good so far. We should get
> > > this working...
> > > The use of the "Round" function however, stands out like a main culprit.
> > > Solver really doesn't like these types of functions.
> > > Here's the reason. Suppose Solver is varying a cell from 4.1, then 4.2,
> > > 4.3., 4.4 ..etc, but the round function keeps this cell at 4. (Rounded
> > > down). It was hoping that a small change would help it determine which
> > > way to go next. If it tries 4.6, the output "Jumps" to 5. ?? Solver
> > > can't figure out what happened??
> > > Also, the Target cell is set to a value of 0. My thoughts...Solver is
> > > looking for an "EXACT" value of zero, but this may not be likely with
> > > integer constraints. You can get "close" to zero, but most likely you
> > > will not reach zero. Solver gives up trying to get closer. Usually, an
> > > alternative is to "Minimize" the target cell, with the added constraint
> > > that the target is >=0. Or more likely Target >=-.005, or something like
> > > this.
> > > I noticed in one part you are trying to choose between two ratio's.
> > > Binary in one constraint, an If( ) function in another cell to pick the
> > > opposite choice, and the use of Sumproduct. The use of If, as was
> > > mentioned, is really a big no-no with Solver.
> > > This is where it comes down to more of an "art" than a science. Your
> > > choices that you are looking for are either 1, or 1/2. A Boolean
> > > constraint is what you want. Have the Boolean value of "1" represent 1,
> > > and a "0" represent 1/2. Suppose you Boolean value is in A1. Then the
> > > choice of gears would be: =(A1+1)/2
> > > Here, when A1 is 1, the output is 1. If 0, then the output is 1/2. This
> > > is how Solver will choose between 1 or 1/2.
> > > Another advantage this simple example has over SumProduct is that it uses
> > > only 1 variable. Sumproduct would require two variables. You are very
> > > close to the 200 changing cell limit of Solver, so every bit helps.
> > > Anyway, hope this helps. I'll take a look at it some more. :>)
> > >
> > > --
> > > Dana DeLouis
> > > Win XP & Office 2003
> > >
> > >
> > > "Dana DeLouis" <[email protected]> wrote in message
> > > news:[email protected]...
> > >> Sounds like you may be using other functions that "Jump." (ie Max, Min,
> > >> VLookUp, If ...) Feel free to send me your workbook. I'd be
> > >> interested in seeing a real world "gear ratio" problem. Thanks. :>)
> > >>
> > >> --
> > >> Dana DeLouis
> > >> Win XP & Office 2003
> > >>
> > >>
> > >> "Rick" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >>>I tried changing the system around based on the IF statement advice. No
> > >>> improvement.
> > >>> For some reason, Solver changes one variable (which happens to have a
> > >>> binary
> > >>> constraint) and makes five attempts without changing anything else. It
> > >>> doesn't even change this one back to zero.
> > >>> Isn't a binary constraint really a form of an IF statement? If zero,
> > >>> than
> > >>> something. If one, then something else. It is not linear.
> > >>>
> > >>> "Dana DeLouis" wrote:
> > >>>
> > >>>> Just to add. You may want to change your initial starting values. By
> > >>>> starting from different numbers, Solver may find a different local
> > >>>> minimum
> > >>>> (or maximum). Make sure your model doesn't use any IF statements, as
> > >>>> this
> > >>>> is one common source of problems for Solver.
> > >>>>
> > >>>> --
> > >>>> Dana DeLouis
> > >>>> Win XP & Office 2003
> > >>>>
> > >>>>
> > >>>> "Rick" <[email protected]> wrote in message
> > >>>> news:[email protected]...
> > >>>> > More information: I just found some information in the help system
> > >>>> > stating
> > >>>> > "When the relative change in the target cell value is less than the
> > >>>> > number
> > >>>> > in
> > >>>> > the Convergence box for the last five iterations, Solver stops."
> > >>>> > This seems to be what's happening. By selecting "Show Iteration
> > >>>> > Results"
> > >>>> > in
> > >>>> > the Options window I can see the five iterations. And nothing is
> > >>>> > changing
> > >>>> > from one attempt to the next. I'm even looking at the numbers out 25
> > >>>> > places.
> > >>>> >
> > >>>> > Why isn't at least one variable changed?
> > >>>> >
> > >>>> <snip>
> > >>>>
> > >>>>
> > >>>>
> > >>
> > >>
> > >
> > >
> >
> >
> >

Hi. Solver will usually start off by not having tight restrictions on the
integer constraints. As it finds a local min (or max), it will start paying
more attention to the integer constraints . Your Solver routine is stopping
short of an answer, so you are looking at constraints that are not quite 0/1
yet.
But Solver works by using tolerances, and not by using "strict" values. For
a small example, suppose A1 was integer, and B1 had the formula:
=(52.8-8.8*A1)
When A1 is 6, B1 should be zero, but it is not. In Excel, the value in B1
is about -0.0000000000000071.
If you are looking to set this equal to 0, it will never happen.
Another way to look at this is if you had another cell with =B1>=0, you
would get False.
However, if A1 had the integer 5.999999, then =B1>=0 would return True.
This is one reason for tolerances.
Hopefully, you can accept 5.999999 as equal to 6. This is just the way
Excel works.
Solver's option for "Tolerance" would apply for Integer problems.
What you are looking for is a solution that is very close to what you want,
but most likely it will not be "exact." What I do in a Solver macro is go
back to all the integer and Binary variables, and round them to the
appropriate integer. Then, reexamine the solution to make sure it is
acceptable.
To give you some idea of the problem, you have 33 options for Gear1, and 52
options for gears 2-3. With the other options, you are looking at
33*52*52*52*6*2*2*2
or

222,723,072 combinations.

Using options like Tolerance, Convergence, Precision, etc may help speed the
process.

In other words, if you are trying to set a target to 0, and one combination
has the Target at 0.0000001, hopefully this will be "good enough." This is
important since as we have seen, Excel may not return an exact 0 anyway.

HTH

--
Dana DeLouis
Win XP & Office 2003


"Rick" <[email protected]> wrote in message
news:[email protected]...
> There's a similar problem with the integer constraint. If I set the
> constraints on the value of a cell to be >=0, <=1, & int, I will still see
> values other than 0 and 1. Why?
>
> "Rick" wrote:
>
>> Just a comment: the "Round" function was put in because the binary
>> constraint
>> doesn't seem to really work. Values such as .999998 or .00001 are used.
>> I've
>> even seen values as far off as .87. These values don't work well in this
>> situation.
>> Why doesn't the binary constraint use values of 0 or 1 only?
>>
>> Rick
>>
>> "Dana DeLouis" wrote:
>>
>> > Just for some additional ideas... Here is one of your cells:
>> >
>> > =IF(E7=1,1,IF(E7=2,8/9,IF(E7=3,8/10,IF(E7=4,8/11,IF(E7=5,8/12,IF(E7=6,8/14,0))))))
>> >
>> > One option would be to make E7 an "Integer" constraint, and add the
>> > constraint that E7 vary from 1-6. Then, the same ratio's from above
>> > could
>> > be calculated as:
>> >
>> > 960/(720+E7*(394+E7*(-225+E7*(85+(E7 - 15)*E7))))
>> >
>> > This is just one way to remove IF statements. There's better equations
>> > that
>> > could help "zero out" the derivative at each point, but this
>> > polynomial
>> > should be good enough to start with.
>> > HTH
>> > --
>> > Dana DeLouis
>> > Win XP & Office 2003
>> >
>> >
>> > "Dana DeLouis" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > Hi Rick. Thanks for the wb. I'm a little slow at understanding it,
>> > > but
>> > > here are my first impressions. It looks like Solver should be a good
>> > > tool
>> > > to use, so you're on the right track. Looks good so far. We should
>> > > get
>> > > this working...
>> > > The use of the "Round" function however, stands out like a main
>> > > culprit.
>> > > Solver really doesn't like these types of functions.
>> > > Here's the reason. Suppose Solver is varying a cell from 4.1, then
>> > > 4.2,
>> > > 4.3., 4.4 ..etc, but the round function keeps this cell at 4.
>> > > (Rounded
>> > > down). It was hoping that a small change would help it determine
>> > > which
>> > > way to go next. If it tries 4.6, the output "Jumps" to 5. ?? Solver
>> > > can't figure out what happened??
>> > > Also, the Target cell is set to a value of 0. My thoughts...Solver
>> > > is
>> > > looking for an "EXACT" value of zero, but this may not be likely with
>> > > integer constraints. You can get "close" to zero, but most likely
>> > > you
>> > > will not reach zero. Solver gives up trying to get closer. Usually,
>> > > an
>> > > alternative is to "Minimize" the target cell, with the added
>> > > constraint
>> > > that the target is >=0. Or more likely Target >=-.005, or something
>> > > like
>> > > this.
>> > > I noticed in one part you are trying to choose between two ratio's.
>> > > Binary in one constraint, an If( ) function in another cell to pick
>> > > the
>> > > opposite choice, and the use of Sumproduct. The use of If, as was
>> > > mentioned, is really a big no-no with Solver.
>> > > This is where it comes down to more of an "art" than a science. Your
>> > > choices that you are looking for are either 1, or 1/2. A Boolean
>> > > constraint is what you want. Have the Boolean value of "1" represent
>> > > 1,
>> > > and a "0" represent 1/2. Suppose you Boolean value is in A1. Then
>> > > the
>> > > choice of gears would be: =(A1+1)/2
>> > > Here, when A1 is 1, the output is 1. If 0, then the output is 1/2.
>> > > This
>> > > is how Solver will choose between 1 or 1/2.
>> > > Another advantage this simple example has over SumProduct is that it
>> > > uses
>> > > only 1 variable. Sumproduct would require two variables. You are
>> > > very
>> > > close to the 200 changing cell limit of Solver, so every bit helps.
>> > > Anyway, hope this helps. I'll take a look at it some more. :>)
>> > >
>> > > --
>> > > Dana DeLouis
>> > > Win XP & Office 2003
>> > >
>> > >
>> > > "Dana DeLouis" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > >> Sounds like you may be using other functions that "Jump." (ie Max,
>> > >> Min,
>> > >> VLookUp, If ...) Feel free to send me your workbook. I'd be
>> > >> interested in seeing a real world "gear ratio" problem. Thanks.
>> > >> :>)
>> > >>
>> > >> --
>> > >> Dana DeLouis
>> > >> Win XP & Office 2003
>> > >>
>> > >>
>> > >> "Rick" <[email protected]> wrote in message
>> > >> news:[email protected]...
>> > >>>I tried changing the system around based on the IF statement advice.
>> > >>>No
>> > >>> improvement.
>> > >>> For some reason, Solver changes one variable (which happens to have
>> > >>> a
>> > >>> binary
>> > >>> constraint) and makes five attempts without changing anything else.
>> > >>> It
>> > >>> doesn't even change this one back to zero.
>> > >>> Isn't a binary constraint really a form of an IF statement? If
>> > >>> zero,
>> > >>> than
>> > >>> something. If one, then something else. It is not linear.
>> > >>>
>> > >>> "Dana DeLouis" wrote:
>> > >>>
>> > >>>> Just to add. You may want to change your initial starting values.
>> > >>>> By
>> > >>>> starting from different numbers, Solver may find a different local
>> > >>>> minimum
>> > >>>> (or maximum). Make sure your model doesn't use any IF statements,
>> > >>>> as
>> > >>>> this
>> > >>>> is one common source of problems for Solver.
>> > >>>>
>> > >>>> --
>> > >>>> Dana DeLouis
>> > >>>> Win XP & Office 2003
>> > >>>>
>> > >>>>
>> > >>>> "Rick" <[email protected]> wrote in message
>> > >>>> news:[email protected]...
>> > >>>> > More information: I just found some information in the help
>> > >>>> > system
>> > >>>> > stating
>> > >>>> > "When the relative change in the target cell value is less than
>> > >>>> > the
>> > >>>> > number
>> > >>>> > in
>> > >>>> > the Convergence box for the last five iterations, Solver stops."
>> > >>>> > This seems to be what's happening. By selecting "Show Iteration
>> > >>>> > Results"
>> > >>>> > in
>> > >>>> > the Options window I can see the five iterations. And nothing is
>> > >>>> > changing
>> > >>>> > from one attempt to the next. I'm even looking at the numbers
>> > >>>> > out 25
>> > >>>> > places.
>> > >>>> >
>> > >>>> > Why isn't at least one variable changed?
>> > >>>> >
>> > >>>> <snip>
>> > >>>>
>> > >>>>
>> > >>>>
>> > >>
>> > >>
>> > >
>> > >
>> >
>> >
>> >

I don't mind so much if the target value doesn’t end up exactly equal to
zero. I set the precision to .0005. In this scenario, that’s close enough. In
some cases, I can live with .005 as close enough. However, a gear with 52.889
teeth doesn’t work very well. Nor does a lever position of .284773 when the
constraints are 0 or 1.
In some of the versions I set up, Solver didn't even change any gears. It
changed a couple levers and gave up.

Any thoughts on another method of finding a set of gears and lever positions
for a desired lead on this lathe? Trial and error isn't the best use of my
time.

Rick

"Dana DeLouis" wrote:

> Hi. Solver will usually start off by not having tight restrictions on the
> integer constraints. As it finds a local min (or max), it will start paying
> more attention to the integer constraints . Your Solver routine is stopping
> short of an answer, so you are looking at constraints that are not quite 0/1
> yet.
> But Solver works by using tolerances, and not by using "strict" values. For
> a small example, suppose A1 was integer, and B1 had the formula:
> =(52.8-8.8*A1)
> When A1 is 6, B1 should be zero, but it is not. In Excel, the value in B1
> is about -0.0000000000000071.
> If you are looking to set this equal to 0, it will never happen.
> Another way to look at this is if you had another cell with =B1>=0, you
> would get False.
> However, if A1 had the integer 5.999999, then =B1>=0 would return True.
> This is one reason for tolerances.
> Hopefully, you can accept 5.999999 as equal to 6. This is just the way
> Excel works.
> Solver's option for "Tolerance" would apply for Integer problems.
> What you are looking for is a solution that is very close to what you want,
> but most likely it will not be "exact." What I do in a Solver macro is go
> back to all the integer and Binary variables, and round them to the
> appropriate integer. Then, reexamine the solution to make sure it is
> acceptable.
> To give you some idea of the problem, you have 33 options for Gear1, and 52
> options for gears 2-3. With the other options, you are looking at
> 33*52*52*52*6*2*2*2
> or
>
> 222,723,072 combinations.
>
> Using options like Tolerance, Convergence, Precision, etc may help speed the
> process.
>
> In other words, if you are trying to set a target to 0, and one combination
> has the Target at 0.0000001, hopefully this will be "good enough." This is
> important since as we have seen, Excel may not return an exact 0 anyway.
>
> HTH
>
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "Rick" <[email protected]> wrote in message
> news:[email protected]...
> > There's a similar problem with the integer constraint. If I set the
> > constraints on the value of a cell to be >=0, <=1, & int, I will still see
> > values other than 0 and 1. Why?
> >
> > "Rick" wrote:
> >
> >> Just a comment: the "Round" function was put in because the binary
> >> constraint
> >> doesn't seem to really work. Values such as .999998 or .00001 are used.
> >> I've
> >> even seen values as far off as .87. These values don't work well in this
> >> situation.
> >> Why doesn't the binary constraint use values of 0 or 1 only?
> >>
> >> Rick
> >>
> >> "Dana DeLouis" wrote:
> >>
> >> > Just for some additional ideas... Here is one of your cells:
> >> >
> >> > =IF(E7=1,1,IF(E7=2,8/9,IF(E7=3,8/10,IF(E7=4,8/11,IF(E7=5,8/12,IF(E7=6,8/14,0))))))
> >> >
> >> > One option would be to make E7 an "Integer" constraint, and add the
> >> > constraint that E7 vary from 1-6. Then, the same ratio's from above
> >> > could
> >> > be calculated as:
> >> >
> >> > 960/(720+E7*(394+E7*(-225+E7*(85+(E7 - 15)*E7))))
> >> >
> >> > This is just one way to remove IF statements. There's better equations
> >> > that
> >> > could help "zero out" the derivative at each point, but this
> >> > polynomial
> >> > should be good enough to start with.
> >> > HTH
> >> > --
> >> > Dana DeLouis
> >> > Win XP & Office 2003
> >> >
> >> >
> >> > "Dana DeLouis" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > > Hi Rick. Thanks for the wb. I'm a little slow at understanding it,
> >> > > but
> >> > > here are my first impressions. It looks like Solver should be a good
> >> > > tool
> >> > > to use, so you're on the right track. Looks good so far. We should
> >> > > get
> >> > > this working...
> >> > > The use of the "Round" function however, stands out like a main
> >> > > culprit.
> >> > > Solver really doesn't like these types of functions.
> >> > > Here's the reason. Suppose Solver is varying a cell from 4.1, then
> >> > > 4.2,
> >> > > 4.3., 4.4 ..etc, but the round function keeps this cell at 4.
> >> > > (Rounded
> >> > > down). It was hoping that a small change would help it determine
> >> > > which
> >> > > way to go next. If it tries 4.6, the output "Jumps" to 5. ?? Solver
> >> > > can't figure out what happened??
> >> > > Also, the Target cell is set to a value of 0. My thoughts...Solver
> >> > > is
> >> > > looking for an "EXACT" value of zero, but this may not be likely with
> >> > > integer constraints. You can get "close" to zero, but most likely
> >> > > you
> >> > > will not reach zero. Solver gives up trying to get closer. Usually,
> >> > > an
> >> > > alternative is to "Minimize" the target cell, with the added
> >> > > constraint
> >> > > that the target is >=0. Or more likely Target >=-.005, or something
> >> > > like
> >> > > this.
> >> > > I noticed in one part you are trying to choose between two ratio's.
> >> > > Binary in one constraint, an If( ) function in another cell to pick
> >> > > the
> >> > > opposite choice, and the use of Sumproduct. The use of If, as was
> >> > > mentioned, is really a big no-no with Solver.
> >> > > This is where it comes down to more of an "art" than a science. Your
> >> > > choices that you are looking for are either 1, or 1/2. A Boolean
> >> > > constraint is what you want. Have the Boolean value of "1" represent
> >> > > 1,
> >> > > and a "0" represent 1/2. Suppose you Boolean value is in A1. Then
> >> > > the
> >> > > choice of gears would be: =(A1+1)/2
> >> > > Here, when A1 is 1, the output is 1. If 0, then the output is 1/2.
> >> > > This
> >> > > is how Solver will choose between 1 or 1/2.
> >> > > Another advantage this simple example has over SumProduct is that it
> >> > > uses
> >> > > only 1 variable. Sumproduct would require two variables. You are
> >> > > very
> >> > > close to the 200 changing cell limit of Solver, so every bit helps.
> >> > > Anyway, hope this helps. I'll take a look at it some more. :>)
> >> > >
> >> > > --
> >> > > Dana DeLouis
> >> > > Win XP & Office 2003
> >> > >
> >> > >
> >> > > "Dana DeLouis" <[email protected]> wrote in message
> >> > > news:[email protected]...
> >> > >> Sounds like you may be using other functions that "Jump." (ie Max,
> >> > >> Min,
> >> > >> VLookUp, If ...) Feel free to send me your workbook. I'd be
> >> > >> interested in seeing a real world "gear ratio" problem. Thanks.
> >> > >> :>)
> >> > >>
> >> > >> --
> >> > >> Dana DeLouis
> >> > >> Win XP & Office 2003
> >> > >>
> >> > >>
> >> > >> "Rick" <[email protected]> wrote in message
> >> > >> news:[email protected]...
> >> > >>>I tried changing the system around based on the IF statement advice.
> >> > >>>No
> >> > >>> improvement.
> >> > >>> For some reason, Solver changes one variable (which happens to have
> >> > >>> a
> >> > >>> binary
> >> > >>> constraint) and makes five attempts without changing anything else.
> >> > >>> It
> >> > >>> doesn't even change this one back to zero.
> >> > >>> Isn't a binary constraint really a form of an IF statement? If
> >> > >>> zero,
> >> > >>> than
> >> > >>> something. If one, then something else. It is not linear.
> >> > >>>
> >> > >>> "Dana DeLouis" wrote:
> >> > >>>
> >> > >>>> Just to add. You may want to change your initial starting values.
> >> > >>>> By
> >> > >>>> starting from different numbers, Solver may find a different local
> >> > >>>> minimum
> >> > >>>> (or maximum). Make sure your model doesn't use any IF statements,
> >> > >>>> as
> >> > >>>> this
> >> > >>>> is one common source of problems for Solver.
> >> > >>>>
> >> > >>>> --
> >> > >>>> Dana DeLouis
> >> > >>>> Win XP & Office 2003
> >> > >>>>
> >> > >>>>
> >> > >>>> "Rick" <[email protected]> wrote in message
> >> > >>>> news:[email protected]...
> >> > >>>> > More information: I just found some information in the help
> >> > >>>> > system
> >> > >>>> > stating
> >> > >>>> > "When the relative change in the target cell value is less than
> >> > >>>> > the
> >> > >>>> > number
> >> > >>>> > in
> >> > >>>> > the Convergence box for the last five iterations, Solver stops."
> >> > >>>> > This seems to be what's happening. By selecting "Show Iteration
> >> > >>>> > Results"
> >> > >>>> > in
> >> > >>>> > the Options window I can see the five iterations. And nothing is
> >> > >>>> > changing
> >> > >>>> > from one attempt to the next. I'm even looking at the numbers
> >> > >>>> > out 25
> >> > >>>> > places.
> >> > >>>> >
> >> > >>>> > Why isn't at least one variable changed?
> >> > >>>> >
> >> > >>>> <snip>
> >> > >>>>
> >> > >>>>
> >> > >>>>
> >> > >>
> >> > >>
> >> > >
> >> > >
> >> >
> >> >
> >> >
>
>
>