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