+ Reply to Thread
Results 1 to 7 of 7

help with excel solver

  1. #1
    Registered User
    Join Date
    01-29-2012
    Location
    Egypt
    MS-Off Ver
    excel 2007
    Posts
    5

    Question help with excel solver

    In excel solver, in some situations during iterations some error values of target cell or contraints can happen such dividing by zero. what should i do in such situation to get the solution ?

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,788

    Re: help with excel solver

    You better upload an example file. Based on your information it is not possible to give any good advice.

    Alf

  3. #3
    Registered User
    Join Date
    01-29-2012
    Location
    Egypt
    MS-Off Ver
    excel 2007
    Posts
    5

    Re: help with excel solver

    I attached the excel file and the example i trying to solve
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,453

    Re: help with excel solver

    Most of the time when I get errors like that, I first check to make sure the spreadsheet is put together correctly and that the algorithm appears to converge correctly.

    One observation, the formula for H appears to be using the group contributions for V instead of H.

    Check what conditions cause the error and see if you should ever be near those conditions. For example, in this case, it appears that a divide by 0 is caused when all of the n(i)'s are 0 -- a 0 length polymer (which is a completely unreasonable condition, isn't it?). Perhaps a condition to specify a minimum polymer size, or a minimum number of methylene groups, or similar.

    Another cause of errors is a poor choice of initial condition. As noted, a 0 length polymer is unreasonably, so using 0's (or anything near 0) for n(i) might be a poor choice for initial conditions.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,788

    Re: help with excel solver

    The constraint for B28 does not look right to me. Formula for B28 is “=18*B25/B22” and this should be less than 0.005?

    Have changed your constraint by setting
    B26 <= G26
    B27 <= G27
    B28 >= G28

    and setting B13 to 1 as an initial starting value so Solver will now find a solution.

    I would recommend when setting up Solver to use “loose” constraints and as soon as a solution is found starting to tighten up the constraints.

    Alf
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-29-2012
    Location
    Egypt
    MS-Off Ver
    excel 2007
    Posts
    5

    Re: help with excel solver

    thanks Alf and MrShorty
    MrShorty your help is very useful for me as there was a mistake in calculating H i adjusted it and it returned a more reasonable solution but not the correct one
    n1 to n7 must be integers
    can you help me more with this ?
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,453

    Re: help with excel solver

    n1 to n7 must be integers
    Assuming the newer versions of Solver have the same options available in my older version, it should be real easy to set a condition that n1:n7 = int, though, at the same time, it might be just as easy to let Solver come to its best non-integer fit, then round the results to the nearest integer.

    it returned a more reasonable solution but not the correct one
    With this kind of problem (7 changeable variables) and the other complexities in this, I'm not at all surprised that there are multiple possible minima. How are you determining that the n1=2/n7=3 solution it converged on is not "the" solution?

    Assuming you are using the default Newton/Raphson method, the way you deal with multiple solutions is to start with different initial guesses. I believe your version of Solver has an optional evolutionary algorithm that may do better at locating a global minima from within the many local minima that are present.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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