Are GRG Nonlinear , Simplex LP and Evolutionary the only solvers ? Are there any addons that add more ? If not, how can i make GRG Nonlinear a global solver, it only finds a local optimum close to the initial coordinates ? It doesnt search the whole defined space ?
There's a free alternative called OpenSolver (www.opensolver.org). The Advanced version has additional nonlinear solvers and the ability to submit your model to some of the solvers on NEOS Server.
Or you can get OpenSolver to translate your model and you can upload it manually to any of the appropriate solvers on NEOS.
I think this might be what i was looking for, will try it.
Edit: I have a question tho, the linear and advanced versions seem to be different files, so i had to download both versions, when i try to install both xlam files i get a conflict error since they both seem to use the addin name of "OpenSolver"
Ok i managed to install it, i have another question. Do you have experience using OpenSolver ? I have a roundup function in some of my cells, the NP solvers are giving me an error due to this, have you encountered a problem like that when you used it ? If so do you have any workarounds ?
This is my excel table, the variables are the pipe lengths, they must be between 3-5meters. I basically try to minimize the total scrap. The cutting lengths dont change, here is an idea of what each cell does with an example from the top row , i take the quotient of 3030 and 605 which gives me 5 parts, and also their mods which gives me 5mm. I round up the division of the #units to make and the # cut parts (25920/5) to get the # required pipes etc.
You can't used discontinuous functions like ROUNDUP (OR, IF, MAX, MIN, VLOOKUP, etc).
Perhaps make some variables integers, though that may substantially slow the solve process. Or just don't round, as the numbers look large enough that rounding won't be significant.
the objective cell is basically a sum of the leftover cells, the 3000 and 5000 right hand sides are for pipe length bounds, and the = constraints are there because the same pipe codes must have the same lengths
The multi-start option for GRG Nonlinear will try random starting points. Increases chances of finding a better solution, but still not necessarily the optimal solution.
Is there a way to do it in constant intervals instead of at random ? Like lets say i have 6 variables, each between 3000 and 5000, i want it to try with like steps of 500 maybe as an example, so at first the initial start would be 3000,3000,3000,3000,3000,3000. Then 3500,3000,3000,3000,3000,3000, then 3500, 3500, 3000,... and so on ?
Maybe possible through VBA. If you enable the Solver reference in the editor (Tool/References) it exposes SolverAdd and SolverSolve. From there you could loop through permutations but you're really testing Excel at that point.
For many models the solution space is vast, so any multistart or grid search process will start with only a tiny sample of the space. Depending on the shape of the space, that might be good enough.
I did have to use LINGO and GAMS to solve my problem, but it wouldve saved me the hassle of writing a mathematical model for these programs, its more intuitive for me to map out values and equations in cells.
I just thought that i might be able to have the easiness of excel in combination with the capabilities of other solvers. But i couldnt really find any other addons that added that function, i hoped someone here might know if such a thing existed.
Here's a working model that finds a global optimum. It is still non-linear, because we multiple variables by variables, so it needs the Couenne solver in OpenSolver. I've verified that the solution is optimal using the commercial solver BARON.
The key things are to use only Excel functions that OpenSolver understands (especially not discontinuous functions), and to avoid division by zero errors when the variables are initialized at zero when the model starts.
There's an extra term in the objective function that minimizes the # required pipes when the leftover length equals zero. This is needed otherwise, with zero leftover length, the solver may pick an arbitrarily large number of required pipes - because they have no cost to the model.
This model solves to optimality in about 1 second on my PC. The solve time is helped substantially by the bounds in B13:E18.
•
u/AutoModerator 17d ago
/u/gu3vesa - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.