Im trying to use Solver to determine gears in a gear train to achieve a
specified ratio input to output. My problem is I dont 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?

Ive 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 ,=20
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 ,
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 ,
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 ,
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 ,=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

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" wrote in message
...
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

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 ,=20
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 ,=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=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