+ Reply to Thread
Results 1 to 3 of 3

How to speed up Solver

  1. #1
    Registered User
    Join Date
    04-04-2014
    Location
    Warsaw
    MS-Off Ver
    Excel 2010
    Posts
    97

    How to speed up Solver

    Hello all,
    Do you have any practical advices how to speed up Solver work. I have to use it in loop, to find a lot of solutions. Finding one solution takes from 20 to even 50 seconds. If I have to solve entire sheet it can take even 5 hours! That's not acceptable at all.

    Here's code that I use:
    Please Login or Register  to view this content.
    Thanks in advance for help!

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

    Re: How to speed up Solver

    1st thought: I recall sitting in a presentation by a researcher in my field. He was describing calculations that would take months to complete. Some problems just by their nature require a lot of operations to solve and, therefore, take time. Since you provide no information about the problem you are solving (other than a snippet of the code that calls Solver), it is impossible for us to make any specific suggestions.

    Have you done some of the common things recommended? Turned screen updating off at the start of your procedure? Are any of your formulas unnecessarily using full column references?

    One way or another, speeding a these things up is about reducing the number of operations. Do you have multiple formulas (array formulas searching a range, in particular) that are duplicating effort? Can you restructure the spreadsheet to reduce this duplication?

    With a Newton-Raphson type algorithm, one way to reduce the number of iterations needed is to give it a better "initial guess"? I obviously cannot tell what is in your by changing cells before the procedure runs. A few lines of code that can put a better "initial guess" into these cells before Solver runs can significantly reduce the number of iterations Solver needs to solve the problem, thus reducing the time needed.

    Those are a few suggestions of common things that can speed this kind of thing up.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    04-04-2014
    Location
    Warsaw
    MS-Off Ver
    Excel 2010
    Posts
    97

    Re: How to speed up Solver

    Thanks a lot MrShorty for this inspiring post, I managed to do this alternatively without using Solver.
    I can't give you reputation right now, but when I'll be able to do this I'll remember to give it to you.

+ 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. macro not keeping solver solutions when solver is successful
    By jimmypants in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2014, 01:45 AM
  2. Replies: 6
    Last Post: 05-18-2013, 05:49 AM
  3. Can't start Solver. Error message says Solver.xlam already open.
    By DaveHills in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 10-21-2012, 11:02 AM
  4. solver macro + simulation code + not updating solver values
    By sabinemaria in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2012, 11:37 AM
  5. Solver speed too low
    By Coaster in forum Excel General
    Replies: 2
    Last Post: 09-09-2010, 10:47 AM

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