+ Reply to Thread
Results 1 to 8 of 8

#N/A's in Solver

  1. #1
    Registered User
    Join Date
    02-07-2015
    Location
    Texas
    MS-Off Ver
    2015
    Posts
    30

    #N/A's in Solver

    Hello - I run a solver every day for a sports program. When I have at least 200 names in the solver it's quite quick. However when there are not 200 players I have to manually delete the #N/A's to run the solver. I have tried to solve (no pun intended) this problem by zeroing out blanks, but I can't figure out the right IF formula.

    See attached sheet below (the formulas still represent my master sheet but the gist of what I'm trying to accomplish is there.)

    In Column D there is a formula that says if the lookup value = 0 then return 0, if not then return a value. What I'd like to do is ONLY run that lookup if there is a value in the column C greater than 0.

    Hopefully the attachment helps - thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: #N/A's in Solver

    Try in D2;
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    And copy down.

  3. #3
    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,758

    Re: #N/A's in Solver

    Looking at your solver model you can set the constraint for the number of players in each position for the range of players and not set min and max for each position one at a time.

    solv_selection.jpg

    Alf

  4. #4
    Registered User
    Join Date
    02-07-2015
    Location
    Texas
    MS-Off Ver
    2015
    Posts
    30

    Re: #N/A's in Solver

    Tried that and I still get the #N/A's.

  5. #5
    Registered User
    Join Date
    02-07-2015
    Location
    Texas
    MS-Off Ver
    2015
    Posts
    30

    Re: #N/A's in Solver

    So the interesting thing here is that changing the solver will allow the equation to solve even with the #N/A's, but the output isn't the maximum output given the parameters. See attached.
    Attached Files Attached Files

  6. #6
    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,758

    Re: #N/A's in Solver

    changing the solver will allow the equation to solve even with the #N/A's,
    Well it should not as the objective cell D203 contains an error i.e. #N/A so when I do a solver run on your uploaded file I do get this result.

    solv_err.jpg

    My proposed change was just to show an alternative method in setting up constraints as this may make the solver setup easier to read when one has a range of values with matching constraints.

    For your original problem I have not got any good ideas. Of course what you do manually could be done using a macro instead but that's not much of an improvement.

    Or perhaps using a "Private Sub" to set the rang for solver to work on as well as the target cell? Some time ago I did set up a solution using range names in solver so perhaps you could use this technique as well?

    In this case you would have to use range name both in solver as well as in the sum function D203.

    https://www.excelforum.com/excel-for...in-solver.html

    But I still think that your original solution is the best, deleting the rows with #N/A value. So if you for example deletes row 184 to 201 and then check your solver setup you will see that solver have adjusted it's settings for all parameters to match the new range.

    And if you insert rows inside the boundaries of the solver model solver will adjust for that.

    Alf
    Last edited by Alf; 02-08-2018 at 04:35 AM.

  7. #7
    Registered User
    Join Date
    02-07-2015
    Location
    Texas
    MS-Off Ver
    2015
    Posts
    30

    Re: #N/A's in Solver

    Got it - thanks much.

  8. #8
    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,758

    Re: #N/A's in Solver

    You are welcome. Thanks for feedback.

    Alf

+ 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. Replies: 7
    Last Post: 12-31-2016, 12:29 PM
  2. Replies: 1
    Last Post: 04-27-2016, 03:05 PM
  3. Replies: 0
    Last Post: 07-20-2014, 12:45 PM
  4. 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
  5. Replies: 6
    Last Post: 05-18-2013, 05:49 AM
  6. 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
  7. 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

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