+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    10-05-2015
    Location
    San Francisco
    MS-Off Ver
    2013
    Posts
    6

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

    Hello, I've used the Solver Add-in before to find out which numbers in a listing add up to a certain sum. However, when I run Solver on the attached data set I get an error, "Too many variable cells". Is there a workaround for this?

    I'm trying to find which numbers add up to a sum of $128,497.08

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,650

    Re: 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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,501

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

    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
    Attached Files Attached Files
    Last edited by Alf; 01-07-2020 at 10:30 AM.

  4. #4
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,501

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

    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
    Last edited by Alf; 01-07-2020 at 11:50 AM.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,078

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

    Hijack post and response have been moved to a new thread: https://www.excelforum.com/excel-for...teria-set.html
    Last edited by AliGW; 07-03-2020 at 03:12 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,501

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

    Post deleted as it's not meaningful.

    Alf
    Last edited by Alf; 07-03-2020 at 03:39 AM.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,078

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

    Alf - whom are you addressing?

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

  8. #8
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,501

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

    Hi Ali

    Taking my time to write a comment I see now that the post I answered to has been deleted/moved from this thread. Moral of story before doing a posting renew thread and see if you answer still make sense.

    Well it did not so I'm deleting comments in this thread #6.

    Alf

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,078

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

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

  10. #10
    Registered User
    Join Date
    05-08-2020
    Location
    Lublin, Poland
    MS-Off Ver
    2003, 2007, 2010, 2019 (home); 2002, 2007, 2016, 365 (work)
    Posts
    69

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

    Quote Originally Posted by MrShorty View Post
    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.
    Przemyslaw Kowalik, Lublin Univ. of Technology

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Create a dynamic range for by changing variable cells box in solver
    By Ad896832 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-26-2017, 02:03 PM
  2. Replies: 4
    Last Post: 12-13-2015, 04:35 PM
  3. [SOLVED] Solver - SolverAdd using variable in variable constrains doesn't work
    By drrazor in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-06-2014, 03:19 PM
  4. Solver dilemma: can variable cells be filled with string values?
    By ndrobinson in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-04-2012, 01:20 PM
  5. Can Solver enter text/strings into variable cells?
    By ndrobinson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-19-2012, 04:06 PM
  6. Using VBA to create a variable "adjustable cells" array in Solver
    By Kybynn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-04-2012, 12:25 AM
  7. Replies: 3
    Last Post: 03-28-2011, 10:53 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1