NON-Linear Solve problem with many criterium

**dontafy** · 08-26-2019, 08:29 PM

Hi Excel Experts.

I need some help with a problem. I need to find a solution group of 3 lines what give me the best possible result.

The result is expressed in sheet "pr" in column S1.
I need to find what lines in the column "AA" can I Change from zero to 1 that gives me the best result.
The maximum number of changes that I can do are 3 (that are shown in U3).

The number needs to be always binary, it means that the column "AB" only can be 0 or 1.

Firstly I thought using "SOLVE" but I have more than 100 variables, that make it impossible.
I also can't change 1 line at a time, see the best result, then searching the second-best result because some times, by change 1 the result doesn't increase, but by changing 2 or 3 simultaneously, it increases a lot.

Could someone help me by finding a different solution?

**MrShorty** · 08-27-2019, 10:26 AM

but I have more than 100 variables, that make it impossible.

Not strictly impossible, but Excel's built in Solver is abitrarily limited to 100 "decision variables" (by changing cells). Other solver engines are not limited in the same way. Maybe finding a more robust solver engine would be possible. Frontline, the makers of Excel's built in Solver, offer paid versions that have much larger limits and, in theory, integrate seemlessly into existing Excel applications. If you are willing to pay the fee (or can complete your task within the 15 day trial period), that might be worth trying. I also see mention on this forum of OpenSolver that may be a useful Solver engine for this larger problem.