×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Solver using a Variable set by a List

Excel Solver using a Variable set by a List

Excel Solver using a Variable set by a List

(OP)
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

RE: Excel Solver using a Variable set by a List

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.

RE: Excel Solver using a Variable set by a List

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)' ?

RE: Excel Solver using a Variable set by a List

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.)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources