Maximizing a solution with Excel Solver

Hello everyone,

i am not sure, if this is the right place to post my problem, but i thought i can't be too wrong with general.
I have a problem, that i try to solve with the solver and i am sure it can be done with it, i just don't know how.
The situation:
I have 38 moons and 36 of them consist of 2 identical corners and 2 arches of diffrent lengths. The other 2 are each 2 arches.
Those corners and arches each consist of 2 types of LED modules in varying quantities: a small and a big one or only one type.
Example: one arch could consist of either 11 big modules and 2 small modules.
Now some of those LEDs got damaged and i listed what got damaged and to how many LED modules of each type this sums up to.
My limiting factor now is the number of small LED modules that i have.

Therefore i would like to maximize the amount of corners and arches that i can replace with the modules i have on stock.
Now i would assume this to be a linear problem, but somehow Simplex LP does not work, so i used GRG-non linear.
Here the problem is that maximized solution is way too low and i can easily get a higher solution by manually tweaking something.
The highest i found so far was 29 repaired corners+arches.

Now my question is how can i optimize my data so solver actually finds the best solution.
I am also really sorry for the formatting, i have not used solver in a long time and i am in a rush to do this, so my formatting is sloppy.
Anyways, i would be really happy about any help! Thanks in advance!

Your setting is a bit strange. The target value N43 is set to 29? Should it not be set to max? The range J2:N39 is defined as "Integer" but on options the box marked "Ignore integer constraints" is marked?

I had a go at your model using OpenSolver with the NOMAD (Non-linear Solver) engine, setting target cell to max, unticking box "Ignore Integer Constraint" and setting precision to 0,00001

The NOMAD engine found a maximum solution of 38 after crunching numbers for about 10 seconds.

As I do not understand your model I have no idea if this is a valid result. To test this you need to download the OpenSolver a freebie from the University of Auckland NZ.

https://opensolver.org/installing-opensolver/

You need to download the "OpenSolver Advanced (Non-Linear)" in order to get NOMAD.

Alf

Perhaps you better check the result I got with OpenSolver and see if it makes sense to you.

Alf

Originally Posted by bsalv

how does your manual suboptimal 29-solution look like ?

The highest i could do manually now was this:

The changed layout confuses me a bit, but as far as i can tell this is the best solution so far. Thanks a lot!
Would have never come to the idea to make the field of the repaired corners + arches the variables that get changed.

Thanks for sharing, was just about to download OpenSolver.
I forgot that i kept the integer=29 when i uploaded it, i just wanted to try if the solver also gets the result i got manually and it did not.
Yes also the range range J2:N39 should have stayed integer, i also just took it off to try things out, because i was at my wits end.

Thanks a lot for the correction of all the formulas, i think i understand now how you have done it and i let increased the time for the calculation and actually got to 42 repaired parts now. (s. attached)
Also it is at actually 163 big modules now, you just had a small error in the sum for the big modules.

Hi bsalv

Very nice model and that you manage to change the setup from a non linear to a linear was a neat trick. Still it seems that the excel solver has a problem with the model.

Running the model after some seconds one sees in the lower left corner of the screen the message "Incumbent: 42,00072" and this value stay the same during the run. So solver finds a solution rather quickly but is not "certain" this is the best solution so it runs through a number of combinations but can't improve the result but it keeps on testing.

You set a cutoff time of 90 seconds. Based on the stable "Incumbent" value I tested using a cutoff time of 5 second and got the same result as you.

I also tested using the linear version of OpenSolve engine CON-OR CBC (Linear solver) and a solution was fond after 0,03 seconds. For linear models OpenSolver is often better than the Excel solver as it's faster and do not being restricted to a max of 200 constraints.

Still that have been cases where the Excel solver gives a better solution, same target value with both solvers but the Excel solver used less number of components.

Just adding my 2¢ of thought on this subject.

Alf