I don't mind so much if the target value doesnt end up exactly equal to
zero. I set the precision to .0005. In this scenario, thats close enough. In
some cases, I can live with .005 as close enough. However, a gear with 52.889
teeth doesnt 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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" 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