Excel Solver using a Variable set by a List

[Recon1775]

Does anyone know of a way to setup a solver scenario in Excel that one of the variables is constrained by a list.
So for example:
Solver would need to find the most optimal bolt size to meet requirements and minimize weight.
So I have a list of fastener cross sectional areas (as shown below) and solver can only pick from this list for the variable change.
How would one set that up in Excel for solver to do that?
0.0147
0.0176
0.0273
0.0327
0.0527
0.0811
0.1096
0.1493
0.1898
0.2411

 

[Denial]

A quick, off-the-cuff thought from someone who has never attempted anything like this. Use an integer as the variable you feed into Solver. This is constrained to lie between 1 and the length of your list (and perhaps to be an integer). If this variable is in cell X15 then in your calculations you use INT(X15+0.5) as the index into your list.

It should be very quick to create a trivial example to explore whether this works. Not quick enough for me now though, as I have a day to begin.

 

[electricpete]

Attached is attempt to do something like suggested.

The input is "inputcell", constrained in solver dialogue to lie between 1 and 7
It selects from among the 7 items in the column "Discrete Variable" (which contains 1.1,2.2,3.3,4.4,5.5,6.6,7.7)

The selected item is listed as "chosen discrete variable" in cell I6

The output cell performs the operation: =10*I6-I6^2

Solver is set to find the max of outputcell as we vary input cell subject to constraints of integer varying between 1 and 7

As shown in the graph, we expect the max would occur when inputcell = 5 and "chosen discrete variable" = 5.5 and output cell = 24.75.

Instead, solver chooses inputcell = 1 and "chosen discrete variable" = 1.1 and output cell = 9.79
It finds the same "solution" regardless of initial value of inputcell between 1 and 7

In fact, even if I make it simpler and get rid of the list and work with outputcell =10*inputcell-inputcell^2, it still doesn't work the way you'd expect

Either I have made an error (very possible), or else solver does not implement integer constraints the way you might expect (by plugging in each integer and trying it out). I recall the underlying continuous algorithm works with partial derivatives which would not be suited to integer constraints. I'm not sure exactly how the algorithm uses those integer constraints.




=====================================
(2B)+(2B)' ?

 

[Denial]

Another day — More time to play.

Thanks for kicking this off, EP. I've been working on your attempt, and will describe what I have done. I am using Excel 2010 on Windows 7, in "compatibility mode". When I use the file as you provided it I get the same (wrong) results you get.

I then noticed that you had constrained inputcell to be binary rather than integer. Fixing this led to:
(Inputcell value before Solver // Inputcell value after Solver)
0<=X<=6.999 // Unchanged (even non-integer values)
7<=X<=inf // Gets right answer (but inputcell will be non-integer)

Next change was to change Solver's Options»GRGnonlinear»Derivative setting from Forward to Central. Now results are:
0<=X<=1.999 // Unchanged (even non-integer values)
2<=X<=inf // Gets right answer (but inputcell will be non-integer)

Next change was to untick the "ignore integer constraints" box in Solver's Options»AllMethods. Results:
0<=X<=1.499 // 1 (which is wrong)
1.5<=X<=1.999 // 2 (which is wrong)
2<=X<=inf // 5 (which is correct)

I could not improve on this, despite trying heaps of things. (Even changing the inputcell>=1 constraint to inputcell>=2 did not get around the problem, it merely moved the "error boundary" from X=2 to X=3.)

My final version of EP's spreadsheet is (hopefully) attached and downloadable.

What seems to be happening is that if the starting value for inputcell points to the first entry in the list, Solver is unable to calculate a derivative so it simply gives up. No error message, which is very naughty: it just leaves inputcell unchanged, except for integer-izing it if requested.

The only way around this that I can see is to set up your list of discrete variables with a dummy first entry, a value that you can be absolutely sure will never be part of your optimum solution, and to ensure that you never use this dummy entry as your starting point. But make sure that this dummy does not introduce an excessive lack of smoothness.

Neat segue into smoothness. I fear that this entire approach will not be reliable if whatever "function" you are feeding the selected list values into is not adequately "smooth" with respect to changes in the index value. See ElectricPete's graph: it is this that needs smoothness. How smooth is "smooth enough"? I have no idea. But I do know that Solver failed when I randomised the order of EP's table. So, if your list contains, say, bolt diameters and strengths and costs, and your optimisation is to seek the cheapest bolt-size / bolt-number combination that meets some strength criterion, then your list should present the bolts in order of size.

Another possible caution. Given that the underlying problem seems to be somehow associated with the calculation of a (secant-based) derivative, there might be some problems if Solver homes in on either the first or the last member of the list. The suggestion two paragraphs above will avoid it homing in on the first member. A similar ruse should probably be applied to avoid the last member.

An interesting, and somewhat revealing, little issue. Thanks for bringing it up. (And anti-thanks for causing me to lose a significant part of my weekend.)