Solver Range Constraint

I just used an extra set of cells and a formula that cuts to zero below 10.

Formula:

Please Login or Register  to view this content.

D3 will ofcourse be in the range "By changing variable cells:"
I have little clue about solver but I'm pretty sure such a non-linearity will make it a lot harder for solver to find a solution.

That's pretty much exact the setup I had. Here is a workbook so you can see what I mean.

The way I see it, it does solve the problem of implementing your constraints. Part of the constraint is implemented in solver and the other part in a formula. Probably not making life easy for Solver but that's what I could come up with. I have minor knowledge of solver, Alf, shg and some other guys here on the forum are known for knowing solver. At least Alf have a tendency to find these threads.

It is difficult to make any specific suggestions based on so little information about the problem. Can you explain the algorithm you are using or wanting to use to optimize this financial portfolio? What is your objective function? How do the decision variables impact the objective function? So much of a Solver problem is less about Solver and more about what you are using for an objective function. When you want to include constraints like this that are non-continuous -- the algorithms have to be even more carefully thought through.

Your profile says you are using 2010. Which Solver algorithm are you using -- evolutionary or GRG non-linear? Evolutionary algorithm is computationally more intense, but often does better with non-continuous functions/constraints. If functions (like if x<10 then 0) tend to create non-continuous functions. So, the evolutionary algorithm may have more success on a problem like this, but it will not be backwards compatible to earlier versions of Excel/Solver.

One strategy I sometimes use for constraints like sum(decision variables)=50 is to hard code the constraint in the spreadsheet rather than adding a constraint to the Solver model. A formula for the last decision variable =50-sum(other decision variables) (then only use the other decision variables in the by changing field in Solver) will force the summation without relying on Solver's numerical algorithms to meet the constraint.

One thought re: if(xi<10,0,xi) -- It might be easiest to Solve the problem first without this condition present. After Solver has optimized the problem once, you can manually go through and set each xi that is less than 10 to 0, then re-run Solver again, removing these 0's from the list of decision variables. Unless something in the model pushes everything below 10, I expect it would only take a couple of "iterations" like this to get the final solution.

Only 1 of my 24 optimized cells is coming out over 0 but less than 10 so it is quite easy to manually move this one value to another cell at the end. Just trying to skip the step of hard entering this and rerunning it again from another array.

It sometimes seems like our goal in life is to automate everything possible. Sometimes we maybe need to think this through. I do a lot of things manually that I'm sure could be automated. I still do them manually because I figure it will take more time and effort to automate them than it takes to do them manually. Sometimes we need to step back and judge whether the effort to avoid a simple manual step is worth the extra effort it will take.

When you tried the evolutionary engine, did it find a different, better solution than the others? Based on what I see, Jacc's IF() function coupled with the evolutionary engine might be the simplest approach to avoiding the manual step -- but only if the evolutionary engine proves able to handle this IF() function.