+ Reply to Thread
Results 1 to 31 of 31

Is Solver capable of solving my problem?

  1. #1
    Registered User
    Join Date
    07-31-2013
    Location
    Troy, MI
    MS-Off Ver
    Excel 2003
    Posts
    24

    Is Solver capable of solving my problem?

    I've got a design spreadsheet where the user has to manually input values (by guessing) to get the design to work. I've got several IF statements that need to be met ("okay" vs. "not okay" return) in order for the design to be acceptable. I've uploaded the file. The cells in blue are the inputs. Can I have Solver determine what values for the inputs will result in each requirement being "okay" (or L3="1")? The inputs have restrictions/parameters however:

    D6 must be a whole number between 20-22
    I6 must be a whole number between 10-12
    D7 must be a whole number
    D8 can be a decimal number between 0.25-0.75

    In other words, I need Solver to give me the inputs (within the parameters mentioned above) that will result in L3 being "1". L3 returns "1" if ALL the individual requirements are met and "No" otherwise.
    Attached Files Attached Files
    Last edited by devpatel85; 01-10-2014 at 12:41 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Is Solver capable of solving my problem?

    The objective function (the formula that Solver attempts to minimize, maximize, or set to a certain value) has to return a number. There's no way for Solver to divine what combination of inputs might return "Yes."
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    07-31-2013
    Location
    Troy, MI
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Is Solver capable of solving my problem?

    You are right. I have set it to return "1" if all requirements are met. I edited my original post.

    Is there a way for me to restrict an input values to whole number as mentioned above?

    Okay, I made an attempt at it (see screenshot), but Solver says that a solution is not feasible, although I know that there is:

    D6=20
    D7=6
    D8=0.47
    I6=10

    screenshot:
    http://i.imgur.com/F5ASpmO.png
    Last edited by devpatel85; 01-10-2014 at 02:18 PM.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Is Solver capable of solving my problem?

    Changing the formula to return 1 instead of Yes doesn't fix anything. It needs to return an (ideally-continuous) numerical value so Solver can calculate the effects of changing each variable to converge to a result.
    Last edited by shg; 01-10-2014 at 02:48 PM.

  5. #5
    Registered User
    Join Date
    07-31-2013
    Location
    Troy, MI
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Is Solver capable of solving my problem?

    So, how would you approach this?

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Is Solver capable of solving my problem?

    You need to apply constraints to each of the conditions that keeps them within their limits, and then have an objective function that measures goodness of fit.

    http://www.solver.com/excel-solver-help is a place to start.

  7. #7
    Registered User
    Join Date
    07-31-2013
    Location
    Troy, MI
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Is Solver capable of solving my problem?

    I don't understand. Can you help me get started? I would really appreciate the help.

    If we're looking at just D28, how would I apply a constraint to it? And what about the objective statement?

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Is Solver capable of solving my problem?

    dp, I know you don't understand. That's why I suggested you take some time to learn how Solver works, and provided a link to a very good tutorial. Once you get the basic concept, someone can help with your particular problem.

  9. #9
    Registered User
    Join Date
    07-31-2013
    Location
    Troy, MI
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Is Solver capable of solving my problem?

    I'll look into it I guess.

    But you are saying that this is possible, right?

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Is Solver capable of solving my problem?

    I haven't looked at your problem closely, but yes, certainly, unless it's very strange.

  11. #11
    Registered User
    Join Date
    07-31-2013
    Location
    Troy, MI
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Is Solver capable of solving my problem?

    Okay, I went through the guide and I'm a little closer to a solution but still unsure about a few things...

    Please correct me if I'm wrong:
    - D6,D7,D8,I6 are my "variables".
    - There are nine requirements in my spreadsheet. These are my "constraints".

    1. What is my objective function? L3=1?
    2. Is my constraint for each requirement the 0/1 return based on the IF statement in that cell or the functions themselves in the IF statement?
    3. How do I apply constraints to my input variables?
    Last edited by devpatel85; 01-12-2014 at 07:22 PM.

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

    Re: Is Solver capable of solving my problem?

    1. What is my objective function? L3=1?
    Correct. As currently programmed, L3 is the objective function and 1 (or Yes or Okay) is the target value. The problem with this is that L3 has only 2 valid results -- 0/1 (or True/False or Okay/Not Okay or Yes/No), which really doesn't work very well as an objective function for Solver.
    Another complication is that there are multiple combinations of values in those inputs that will result in a Yes/1/true result.

    A better objective function might be D27, where you ask Solver to determine when does D27=I8 (or, in a form that Solver better understands, when does D27-I8=0). Or maybe D102, where you can instruct Solver to find when D102-D41=0. In reality D27 and D102 are still not a very good objective functions because they appear to be "step" functions, it's value changes in discreet steps rather than changing smoothly.

    In the end, you want your objective function to be a smooth, continuous function that you can readily tell Solver to find a target value or find the max/min.

    3. How do I apply constraints to my input variables?
    In the Solver dialog, there should be a "contraints" button that you press to enter the constraints dialog where you can specify the constraints for your input variables.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  13. #13
    Registered User
    Join Date
    07-31-2013
    Location
    Troy, MI
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Is Solver capable of solving my problem?

    Still VERY confused haha. And not convinced whether this is even possible, although it seems as if it should be.

    L3=1 is not a good objective statement as you say. There can only be one target cell, however. What else can I make it? I can't have multiple objective statements for each requirement (more than one Solver runs), can I?

    For the most part, I understand how Solver works, but I'm not sure how to apply it to my situation.
    Last edited by devpatel85; 01-13-2014 at 02:01 PM.

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

    Re: Is Solver capable of solving my problem?

    Perhaps if we simplify the problem.

    I observe that, if we leave D7, D8, and I6 unchanged, and only change D6, that L3 is "yes" for D6=19,20,21. When L3 is no and D6>21, D102 is the function that is "not okay".
    When D6<19, D41 is the function out of range. I also note that the test in D103 is simply comparing D41 and D102 for which is larger. So a possible Solver model to find D6 (other values as given) might be "Set D41 to a minimum by changing D6 subject to constraints that D6 is an integer between 20 and 22 and that D41>=D102."

    I would probably perform a similar exercise for each of the other 3 input variables to get a better feel for how each one influences the 7 check functions. The goal would be to identify one of the 7 output functions that seems "most important" to the analysis. Figure out how that function changes as the input variables change so you can figure out what to use for the target value for that function. Then figure out what constraints to put on the input and output functions within Solver so that Solver can find the desired solution (or one of the many possible solutions).

  15. #15
    Registered User
    Join Date
    07-31-2013
    Location
    Troy, MI
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Is Solver capable of solving my problem?

    Doesn't all this extra effort defeat the purpose of Solver? I was hoping for a way to enter my inputs (not the decision variables but cells like D3,D5,I5, etc.) and have Excel determine which values for D6,D7,D8, and I6 would satisfy all the requirements.

    What would Solver look like for D8? It directly/indirectly effects multiple cells.

    Thanks for your help btw.
    Last edited by devpatel85; 01-13-2014 at 05:48 PM.

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

    Re: Is Solver capable of solving my problem?

    Doesn't all this extra effort defeat the purpose of Solver?
    Only if, in the process of developing the spreadsheet/solver model, we end up solving every problem you will ever have and never need to use this spreadsheet/solver model.

    To explore what happens with D8, enter different values in D8 to find where something goes out of range. For example, if I increase D8 to 0.5, everything is still in range, but if I increase it to 0.51, something is out of range. It looks like it is D41 again. Do the same for decreasing D8, where D102 seems to be the check that fails first.

    One thing I notice with D8 is that D102 is a "step" function of D8 -- D102 stays constant for a relatively wide range of values for D8, then "steps" with the next change in D8. Solver's algorithms are designed around the Newton-Raphson method, which does not work very well for "step" or other discontinuous functions. (Here's a good tutorial on how Newton's method works in 2 dimensions http://www.cs.utah.edu/~zachary/isp/...ot/Newton.html It might be worthwhile to review this to understand the basic idea behind Solver's algorithms.)

    It is beginning to look to me like D102 might be a good objective function, if it can be "smoothed" out relative to the inputs so that it changes smoothly.

  17. #17
    Registered User
    Join Date
    07-31-2013
    Location
    Troy, MI
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Is Solver capable of solving my problem?

    Sorry about the late response:

    Honestly, this is going well above my head.

    If I am understanding you correctly, I should be able to achieve this using just one Solver model with D102 as the objective function since it's the one that is controlling? How would you setup the rest of the model? Would my constraints be each individual requirement I have defined, or the limits on the decision variables?
    Last edited by devpatel85; 01-17-2014 at 01:59 PM.

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

    Re: Is Solver capable of solving my problem?

    This looks like ME, and, since ME is not my field (I'm more into ChemE), I can appreciate how difficult it is to solve a problem like this when it is 'above your head'. I'm a firm believer that one needs to first understand the math behind a problem before delving into the spreadsheet programming. Since the math appears to be above both our heads (at the moment anyway), I can appreciate the frustration. This is also why I tried earlier to "simplify" the problem to one variable at a time. I find that learning something new in math often needs to start with concepts I already understand, then extend those concepts into something I don't understand. Often when I encounter a problem I don't understand, I try to simplify it down to something I do understand, then build back up to the original problem.

    If I am understanding you correctly, I should be able to achieve this using just one Solver model with D102 as the objective function since it's the one that is controlling?
    I would say that this is more of a "hypothesis" than that I think D102 is the smoking gun for solving the problem. I think shg's suggestion (post #6) where he suggests that this might be better approached as a "curve fitting" type problem where we optimize some measure of "goodness of fit" might also provide a solution to the problem.

    Whatever the solution ends up being, my next step (if I were going to continue working on this problem) would be to grab a sheet of paper (or maybe a ream) and write down the function in D102. Then look at each of the references that feeds into D102 and write that function down. Continue until I had all the equations written down going from D102 back to the 4 input variables. Then I would play around with the algebra of that group of functions until I felt that I had a good grasp of the relationships between D6, D7, D8, I6 and D102. This would include identifying discontinuities, "steps", regions where D102 is not changing, maxima, minima, etc. Keeping in mind that I was looking for something that a NR type algorithm can solve, I would then evaluate whether some variation of D102 is a good choice for an objective function or not. Then see where it goes from there.

  19. #19
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,449

    Re: Is Solver capable of solving my problem?

    as you don't have an objective function, you can make excel guess your 4 variables within their constraints and loop untill he finds a solution or untill you're too tired to wait.
    Please Login or Register  to view this content.

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

    Re: Is Solver capable of solving my problem?

    I like bsalv's idea -- try a Monte Carlo approach to the problem -> throw random numbers at it until a solution is found. Might be easier than trying to get the NR algorithm working. I guess it would depend on whether or not the problem "must" be solved using Solver, or if any workable solution is acceptable.

  21. #21
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,449

    Re: Is Solver capable of solving my problem?

    you could run my macro in a loop for 100 times, so you have 100 feasible solutions and then find the best D102-value in those solutions.

  22. #22
    Registered User
    Join Date
    07-31-2013
    Location
    Troy, MI
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Is Solver capable of solving my problem?

    I'll try your suggestion bsalv. I'm not so familiar with VBA and macros and such, so I'll have to do a bit of reading up on it first.
    Last edited by devpatel85; 01-20-2014 at 12:54 PM.

  23. #23
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,449

    Re: Is Solver capable of solving my problem?

    Please Login or Register  to view this content.

  24. #24
    Registered User
    Join Date
    07-31-2013
    Location
    Troy, MI
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Is Solver capable of solving my problem?

    This works pretty well bsalv. I have a few questions...

    Please Login or Register  to view this content.
    Last edited by devpatel85; 01-20-2014 at 05:49 PM.

  25. #25
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,449

    Re: Is Solver capable of solving my problem?

    1. there is no screenupdating in de "do...Loop", but as soon as a solution is found, that one is shown. Look where i wrote both lines.
    2. Application.StatusBar = False if you omit that one, when the macro stops, the statusbar 'll still show "solution 100" (or another number) and that's not an error but it isn't nice.
    3.
    PHP Code: 
    .Offset(-1).Resize(1).Value = Array("D6""D7""D8""I6"
    the row just above says where you want to write your data, but with this one, we write a header just above, so the offset is -1 (1 row above) and the size is 1 row and also 4 columns (but that's the same, so you don't have to repeat that).
    4. If you want to exchange columns, you have to do that in
    PHP Code: 
    .Item(i) = Array(Range("D6").ValueRange("D7").ValueRange("D8").ValueRange("I6").Value
    and then in the header (see 3) and if the sortcolumn changes make that ".cells(1)" for example ".cells(2,1)" for the 2nd column. There is no header because with that "with...end with" construction you only look at the range Range("O2").Resize(MyArray.Count, 4), so the header isn't included.

  26. #26
    Registered User
    Join Date
    07-31-2013
    Location
    Troy, MI
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Is Solver capable of solving my problem?

    - If there are no possible solutions, can I get the macro to display a message instead of trying to keep searching? The only way for me to end it is by pressing escape.
    - I want to have two cells in the spreadsheet that specify the min/max values for D6 (may not always be 20-22). How would I alter the code to reference those cells instead of using:
    Please Login or Register  to view this content.

  27. #27
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,449

    Re: Is Solver capable of solving my problem?

    - after 10.000 loops and no feasible solution, the macro stops.
    - in M6:N9 you can add your limits
    I don't know why, but now i get no solution ???
    Attached Files Attached Files

  28. #28
    Registered User
    Join Date
    07-31-2013
    Location
    Troy, MI
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Is Solver capable of solving my problem?

    That's odd. I got it to work using this code:

    Please Login or Register  to view this content.
    Made some small adjustments. Works great, I might make some minor refinements later. Thanks for the help guys.

  29. #29
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,449

    Re: Is Solver capable of solving my problem?

    perhaps a small adjustment
    after the "Application.ScreenUpdating = True" you change him immediately to "Application.ScreenUpdating = false" or you omit that row.
    So the macro 'll find quicker its solutions, because the screen doesn't have to update.

  30. #30
    Registered User
    Join Date
    07-31-2013
    Location
    Troy, MI
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Is Solver capable of solving my problem?

    Before Application.StatusBar = "Solution " & i, you are saying to add Application.ScreenUpdating = False?

    Another thing: if after 10,000 loops, there are 7 solutions, can I get it to show those solutions? It's only if there are 0 solutions that I want it to say "There is no feasible solution".
    Last edited by devpatel85; 01-24-2014 at 09:30 AM.

  31. #31
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,449

    Re: Is Solver capable of solving my problem?

    this macro stops after 100 solutions or 10.000 loops. I hope it works, because the macro didn't find a solutions during this 10.000 loops
    My reaction #29 was an error.
    Please Login or Register  to view this content.

+ 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. Solving equation using the Solver Function of Excel
    By ltomilas in forum Excel General
    Replies: 2
    Last Post: 08-29-2013, 04:13 PM
  2. [SOLVED] Solving Engineering problem using Solver
    By mukund23534 in forum Excel General
    Replies: 5
    Last Post: 12-17-2012, 03:31 PM
  3. [SOLVED] Solver problem - macro to execute a Evolutionary Solving Method
    By timtim89 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-01-2012, 02:18 PM
  4. Solving a problem within VBA without using solver
    By xodus8 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-23-2011, 03:53 PM
  5. [SOLVED] Excel Solver is not retaining correct constraints when solving
    By GottaGetItRight in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-06-2005, 11:30 PM

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