Too many variable cells for Solver - Numbers that add up to a specific value

The built in Solver that come bundled with Excel is limited to 100 or 200 "decision variables" (by changing cells). I looks like you want 300 "or so" by changing cells. This is just an artificial limitation built into the free version that comes with Excel.

There is no workaround using the built in utility. The workaround is to get a different solver engine that can handle larger problems. If you are willing to pony up the money, Frontline (the developers of the built in Solver) offer larger solver engines that should integrate seamlessly into Excel. If you want to try an open source project, I am aware of the OpenSolver project that can handle larger problems (though it doesn't seem to like my problems when I have tried it). Further searching may find other alternatives specific to subset sum problems (that work within Excel or are stand-alone applications). Of course, if you are ambitious, you could program your own trial and error subset sum algorithm in the spreadsheet.

That's probably not the answer you wanted, but there it is. Let us know what solutions you find for these larger subset sum problems.

There is a freebie called OpenSolver build by the University of Auckland NZ that integrates nicely with excel you could try to use instead as there is no artificial limits
set on the size of problem you can solve.

I've rebuild your model a bit and as OpenSolver can "read" excel solver models I do prefer to build my models using the excel solver and then run them with OpenSolver.

Link to OpenSolver

https://opensolver.org/

Alf

Did not check my original uploaded file that has the the optimal result since all numbers end with .00, .50 or .05 so there is no possible way a result could end in .08.

Earlier comments deleted.

Alf

Hijack post and response have been moved to a new thread: https://www.excelforum.com/excel-for...teria-set.html

Post deleted as it's not meaningful.

Alf

Alf - whom are you addressing?

The hijacker, his post and Hydraulic's response are no longer here ...

LOL! No worries ... I've added a link to the hijack post's new thread, if you want to help out there.

Originally Posted by MrShorty

The built in Solver that come bundled with Excel is limited to 100 or 200 "decision variables" (by changing cells). I looks like you want 300 "or so" by changing cells. This is just an artificial limitation built into the free version that comes with Excel.

There is no workaround using the built in utility. The workaround is to get a different solver engine that can handle larger problems.

In general, you are right about the limitation of the "decision variables" in built in Solver and non-existence of workaround of this limitation. But in case of this particular problem it is possible.
I noticed that many parameters - numbers in B2:B303 have the same values. In particular, 48 of them are zeroes. This means that they don't affect the objective function and the "true" number of the variables is not 301 but 301-48=253. Still too many, of course but let's continue. We have the value 95 occuring 40 times, so we can replace 40 binary variables with one integer variable with extra limits >=0 and <=40. We "saved" 39 variables so now it's 253-39=214. The same trick can be done with 308.75 (8 occurences, 7 variables saved) and 332.5 (also 8 occurences, 7 variables saved) and we have 200 variables: one integer with extra limits >=0 and <=40, two integer with extra limits >=0 and <=8 and 197 binary. Of course, further reduction of the number of variables resulting from repetition of parameters' values is possible.