How to make XL2010 Solver integer constraints work? (Or don't they?)

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,960
Office Version
  1. 2010
Platform
  1. Windows
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:
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Binary and integer constraints operate within the Constraint Precision setting in Options (my speculation is to calculate partial derivatives toward a solution). Formulas need to accommodate that, but that in turn may lead to flat spots that can befuddle Solver.
 
Last edited:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,960
Office Version
  1. 2010
Platform
  1. Windows
Crossposted to another forum (click here). [1]
 
Last edited by a moderator:
Solution

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
You're welcome.

Oh ... never mind.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top