XL2010 Solver integer constraints do not seem to work in the following example. Any idea how to make them work? Or is the feature simply broken or unreliable?
Download the file "solver integer constraints.xlsx" (click here) [1]. Ignore any preview error, and just proceed with the download.
I have the following formula in A5:
A4 is 1 initially in order to avoid an Excel error.
I want XL2010 Solver to find the minimum non-negative value in A5 subject to the following constraints:
A4 = integer
A4 >= 2
A5 >= 0
Make unconstrained variables non-negative (checked)
Method: GRG nonlinear
Options: All methods: (do not) Ignore integer constraints (unchecked)
At first, Solver aborted with a #REF error in A5 because A4 was not an integer (about 2.000004, IIRC), notwithstanding the constraints and options. Now, Solver behavior has changed inexplicably. It no longer aborts with an Excel error in A5; but the "solution" is simply the initial value in A4. (Hmm, after saving as "xlsx", closing and reopening the file, the #REF error has returned. YMMV.)
The formula seems to be "continuous" for positive integer values in A4. That is, the result seems to decrease continuously as the value in A4 increases. And empirically, I find that A4=13 produces the minimum non-negative result in A5 (about 56,566.78).
I did try the other two Solver methods, to no avail. I also tried the constraint A4>=1, which is sufficient on second thought, again to no avail. And I tried changing A4 to INT(A4) in all references in the formula, again to no avail.
PS.... Ironically, I answered this question myself in another Excel forum. But in that case, the problem seemed to be that the option "Ignore integer constraints" defaulted to enabled (checked). The simple solution was to disable the option (unchecked); and that seemed to work for that OP. But as noted above, I did just that; and it does not seem to work for me.
-----
[1] https://app.box.com/s/h7eklba9sg69bco3vg16ffdhar9sgrv0
Download the file "solver integer constraints.xlsx" (click here) [1]. Ignore any preview error, and just proceed with the download.
I have the following formula in A5:
Code:
=1000000*(1+7%)^A4 - 100000*SUMPRODUCT((1+7%)^(A4-ROW(INDIRECT("1:" & A4))),(1+3%)^(ROW(INDIRECT("1:" & A4))-1))A4 is 1 initially in order to avoid an Excel error.
I want XL2010 Solver to find the minimum non-negative value in A5 subject to the following constraints:
A4 = integer
A4 >= 2
A5 >= 0
Make unconstrained variables non-negative (checked)
Method: GRG nonlinear
Options: All methods: (do not) Ignore integer constraints (unchecked)
At first, Solver aborted with a #REF error in A5 because A4 was not an integer (about 2.000004, IIRC), notwithstanding the constraints and options. Now, Solver behavior has changed inexplicably. It no longer aborts with an Excel error in A5; but the "solution" is simply the initial value in A4. (Hmm, after saving as "xlsx", closing and reopening the file, the #REF error has returned. YMMV.)
The formula seems to be "continuous" for positive integer values in A4. That is, the result seems to decrease continuously as the value in A4 increases. And empirically, I find that A4=13 produces the minimum non-negative result in A5 (about 56,566.78).
I did try the other two Solver methods, to no avail. I also tried the constraint A4>=1, which is sufficient on second thought, again to no avail. And I tried changing A4 to INT(A4) in all references in the formula, again to no avail.
PS.... Ironically, I answered this question myself in another Excel forum. But in that case, the problem seemed to be that the option "Ignore integer constraints" defaulted to enabled (checked). The simple solution was to disable the option (unchecked); and that seemed to work for that OP. But as noted above, I did just that; and it does not seem to work for me.
-----
[1] https://app.box.com/s/h7eklba9sg69bco3vg16ffdhar9sgrv0
