I'm having positing problems to boot, so I don't even know if this will make
it to the newsgroup, but I hope so. If it does, I'll see it. Though I
seldom use solver, I recall it to be powerful. I wanted to show some other
users how nice it is, but it is not working for me. I concocted an example
with only nine integer choices for an input, values of 1 thru 9. I asked it
to maximize an output cell. Using a data table, I can quickly see that the
output cell's curve is concave down and definitely has a clear maximum in
about the middle. Bu, for some reason, Solver will not budge from whatever
starting integer value I give it. It says it has found a solution but the
solution is always whatever value it had when I started the solver.



I am simply saying maximize the output cell subject to constraints that the
input cell (that I am changing) being an integer, being greater than or
equal to 1, and being less than or equal to 9. I have checked and rechecked
and there just doesn't seem to be any reason this is not working. It can
find the minimum (which is at one of the two endpoints) just fine. I notice
that it takes about 20 seconds (and you see some fleeting calcs at the
bottom left) to find that minimum, whereas it thinks it finds the maximum in
about 10 seconds, and you don't see much of the fleeting calculation.



Can anyone explain how this could happen? I don't think I'm doing something
stupid, I tried an example with a much simpler output cell, output cell
equals input cell plus 2, and it found that constrained maximum just fine,
so the solver seems to be working. I can't fathom what the problem can be.
I've used it before and it solved much harder problems.



Thanks!

Dean

I am not familiar with Solver, since I haven't used it, but I can guess
that maybe the problem is with your forcing the input to be (or round off
to) an integer. A lot of mathematical techniques attempt to take a small
step in one direction, then effectively compute deltaY/deltaX. In this
case, you end up with 0/0, which is indeterminate.

I am guessing that maybe you should try removing the integer requirement
from the input variable, let Solver find the maximum, then round the input
value off to the nearest integer, or evaluate the result at both integral
values on both sides of the result, then choose the best value.

Solver may not be designed to work with problems where the input variable
is a quantized variable in this fashion. What does the documentation say?
--
Regards,
Bill Renaud

No - one of the canned constraints is that it be an integer, so it's
definitely allowed. Nevertheless, I took it out and it didn't help. I took
out all the constraints and that didn't help either. It's really bizarre
to me. Solver actually says it has found a solution, but the solution is
always the same as whatever value you start it off with. I was thinking
that I had too high numbers for some of the options, such as convergence,
etc, but I set them all really low, as EXCEL help suggested to do, and it
did not help.

D

"Bill Renaud" wrote in message
. ..
I am not familiar with Solver, since I haven't used it, but I can guess
that maybe the problem is with your forcing the input to be (or round off
to) an integer. A lot of mathematical techniques attempt to take a small
step in one direction, then effectively compute deltaY/deltaX. In this
case, you end up with 0/0, which is indeterminate.

I am guessing that maybe you should try removing the integer requirement
from the input variable, let Solver find the maximum, then round the input
value off to the nearest integer, or evaluate the result at both integral
values on both sides of the result, then choose the best value.

Solver may not be designed to work with problems where the input variable
is a quantized variable in this fashion. What does the documentation say?
--
Regards,
Bill Renaud

Does the vendor have a tech support group or peer-to-peer self-help message
board (like this one)? You might have to repost this question (with a
little more detail about the function you are using) to that forum.

--
Regards,
Bill Renaud

Are you using the Excel Solver add-in, or the one from Frontline Systems?
If using the Excel Solver, then post your worksheet setup (cell values and
formulas) or post to the worksheet functions newsgroup.

--
Regards,
Bill Renaud

I am using the EXCEL solver. Now that you mention Frontline, it rings a
distant bell in my memory, such as them sending me stuff about some of their
more advanced stuff. I am hoping that the EXCEL version is capable of
understanding its own NPV function but am, now, starting to wonder if it
can't handle that one; and that frontline told me that 10 years ago!

Thanks! Dana has offered to help but if he can't, I'll post where you say -
I assume you mean that this is another Microsoft newsgroup.

D

"Bill Renaud" wrote in message
. ..
Are you using the Excel Solver add-in, or the one from Frontline Systems?
If using the Excel Solver, then post your worksheet setup (cell values and
formulas) or post to the worksheet functions newsgroup.

--
Regards,
Bill Renaud

I set up a sample problem similar to your description, and it seems to work
fine (I use Excel 2000).

B1: value
B2: =-1*((B1-5)^2)+10

This is a parabola, with the maximum at 5 and cupped downward, like you
mentioned.

Solver Parameters:
---------------------
Set Target Cell: $B$2
By Changing Cells: $B$1
Subject to the Constraints:
$B$1 <= 10
$B$1 = integer
$B$1 = 0

Options: (I left everything at the defaults)
--------------------------------------------
Max Time: 100 seconds
Iterations: 100
Precision: 0.000001
Tolerance: 5%
Convergence: 0.0001

All checkboxes OFF (Assume Linear Model, Assume Non-Negative, Use Automatic
Scaling, Show Iteration Results).

Estimates: Tangent
Derivatives: Forward
Search: Newton

I could start with cell $B$1 at 0 or 10, and it would converge to
4.9999999824217 in about 1 second.

--
Regards,
Bill Renaud

Oh, I missed this. Yes, I also tried the default settings. Believe me,
I've used solver before in the past with no such problems, for generally
hairier workbooks than this.

Dean

"Bill Renaud" wrote in message
...
I set up a sample problem similar to your description, and it seems to work
fine (I use Excel 2000).

B1: value
B2: =-1*((B1-5)^2)+10

This is a parabola, with the maximum at 5 and cupped downward, like you
mentioned.

Solver Parameters:
---------------------
Set Target Cell: $B$2
By Changing Cells: $B$1
Subject to the Constraints:
$B$1 <= 10
$B$1 = integer
$B$1 = 0

Options: (I left everything at the defaults)
--------------------------------------------
Max Time: 100 seconds
Iterations: 100
Precision: 0.000001
Tolerance: 5%
Convergence: 0.0001

All checkboxes OFF (Assume Linear Model, Assume Non-Negative, Use
Automatic
Scaling, Show Iteration Results).

Estimates: Tangent
Derivatives: Forward
Search: Newton

I could start with cell $B$1 at 0 or 10, and it would converge to
4.9999999824217 in about 1 second.

--
Regards,
Bill Renaud

Could you give us the function of your Target Cell?
Feel free to send me your workbook, and I'llbe glad to take a look at it.

--
Dana DeLouis


"Dean" wrote in message
...
No - one of the canned constraints is that it be an integer, so it's
definitely allowed. Nevertheless, I took it out and it didn't help. I
took out all the constraints and that didn't help either. It's really
bizarre to me. Solver actually says it has found a solution, but the
solution is always the same as whatever value you start it off with. I
was thinking that I had too high numbers for some of the options, such as
convergence, etc, but I set them all really low, as EXCEL help suggested
to do, and it did not help.

D

"Bill Renaud" wrote in message
. ..
I am not familiar with Solver, since I haven't used it, but I can guess
that maybe the problem is with your forcing the input to be (or round off
to) an integer. A lot of mathematical techniques attempt to take a small
step in one direction, then effectively compute deltaY/deltaX. In this
case, you end up with 0/0, which is indeterminate.

I am guessing that maybe you should try removing the integer requirement
from the input variable, let Solver find the maximum, then round the
input
value off to the nearest integer, or evaluate the result at both integral
values on both sides of the result, then choose the best value.

Solver may not be designed to work with problems where the input variable
is a quantized variable in this fashion. What does the documentation say?
--
Regards,
Bill Renaud

It's an NPV function, which is a polynomial. I'll e-mail it to you.

Thanks much!
Dean

"Dana DeLouis" wrote in message
...
Could you give us the function of your Target Cell?
Feel free to send me your workbook, and I'llbe glad to take a look at it.

--
Dana DeLouis


"Dean" wrote in message
...
No - one of the canned constraints is that it be an integer, so it's
definitely allowed. Nevertheless, I took it out and it didn't help. I
took out all the constraints and that didn't help either. It's really
bizarre to me. Solver actually says it has found a solution, but the
solution is always the same as whatever value you start it off with. I
was thinking that I had too high numbers for some of the options, such as
convergence, etc, but I set them all really low, as EXCEL help suggested
to do, and it did not help.

D

"Bill Renaud" wrote in message
. ..
I am not familiar with Solver, since I haven't used it, but I can guess
that maybe the problem is with your forcing the input to be (or round
off
to) an integer. A lot of mathematical techniques attempt to take a small
step in one direction, then effectively compute deltaY/deltaX. In this
case, you end up with 0/0, which is indeterminate.

I am guessing that maybe you should try removing the integer requirement
from the input variable, let Solver find the maximum, then round the
input
value off to the nearest integer, or evaluate the result at both
integral
values on both sides of the result, then choose the best value.

Solver may not be designed to work with problems where the input
variable
is a quantized variable in this fashion. What does the documentation
say?
--
Regards,
Bill Renaud

(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