Interesting that, despite what you refer to as giving up, Solver always says
it has found a solution. I guess that's part of the known issue. The EXCEL
imprecision is something that I recall from long ago and, to this day, I
will often write if statements that say, if (abs(A-B) <0.00000001, ...)
which confuses readers but covers tiny roundoff. That said, slowly over the
years, I do this less often since I don't notice the problem being there.
Perhaps it was only there, even back then, when using things like solver.

It will take me a little while to understand your workaround but, in the
meantime, I first tried replacing all the integer cell references "X" with
round(X,0) but that did not help. I then replaced all if statement
arguments with:

abs(x-y)<-0.1, abs (x-y) 0.1, or abs(x-y)< 0.0001, Since x-y will always
be infinitesimally close to an integer, this should work. But it didn't.
Assuming I didn't miss one (and my plot of the data, table results seems to
confirm the 9 values are unchanged - also if I try 3.99999, rather than 4,
manually it produces the same answer), shouldn't this have also worked as a
workaround? Or, perhaps, I am missing the point.

Thanks so much for your analysis.

Dean

"Dana DeLouis" wrote in message
...
Hi. Got your sheet. I can see what the problem is.
Although your Target Cell does not use any "Discontinuous functions," the
Target Cell is dependent on many cells that do use these functions.
The Target Cell is dependent on many uses of the IF function. This is the
problem !!

But, for some reason, Solver will not budge from whatever starting
integer value I give it.

I'll try to give a quick explanation as to the problem.
In a nutshell, Solver can easily get confused, and when it does, will
often give up immediately without a warning. This is a known issue.

Lets look at one of your dependant cells.
=IF(G12$I$26,0,IF(G12<$I$26,0,-$I$7)+$I$7*$E$27*-1)

(G12 and I26 represent Integer Years)

Even if G12 were "suppose" to equal I26, and tolerances in Excel might
show these as unequal. Hence, a problem.
Suppose we do start off with an integer guess like you mention.
Only 1 of these cells would show a value and in theory would work as
planned.
However, on the very next guess, the value of your changing cell would
most likely "NOT" be a true integer. (Even though the constraint is
integer).
Excel does not start out with most of the integer constraints set at true
integers. Later on, it will try to get them "closer" to integer values.
So, on the next guess, "ALL" of the equations like that above come into
play. This is enough to confuse Solver, and will quit immediately.
That is one reason the use of IF functions will not work in Solver.

Your equation above might be written like this:
IF G12 = "Integer Changing Cell, then ...

This would almost NEVER be true.

The whole model would have to be re-written.
It appears to me that one solution might be the following.
Select 9 Cells as your changing cells. Make the constraint "Binary" (ie
0 or 1)

Also, in another cell, Sum these 9 Cells.
Add a constraint that the sum of these 9 cells =1.
(Actually, I might use =0.9 and <= 1.1 since we want to avoid problems
of equality.

Now, Solver will put a 1 in only 1 of the 9 Cells. It is up to you to use
that 1 value and calculate a value.
The cell that ends up with a 1 represents the number of years.
Anyway, hope this helps a little.

--
Dana DeLouis



"Dean" wrote in message
...
Yes, it would take too long for folks to recreate, plus I doubt it would
provide any insight to the problem. If there is somewhere I could post
the spreadsheet, I'd be happy to do so. It is a small one, albeit with
some hairy equations.

Thanks for asking, Bill
Dean

"Bill Renaud" wrote in message
. ..
(Excel Solver add-in that won't converge.)

Dean wrote:
<<It's an NPV function, which is a polynomial.

Is the setup too complex to post here in the newsgroup, for the benefit
of
other viewers?
--
Regards,
Bill Renaud