+ Reply to Thread
Results 1 to 15 of 15

Using Solver to change text value

  1. #1
    Forum Contributor
    Join Date
    06-27-2013
    Location
    Nebraska
    MS-Off Ver
    Office 365
    Posts
    115

    Using Solver to change text value

    I am currently using the Sovler add-in to try and minimize the cost/unit produced of multiple product lines. My sheet is currently setup with a column that you can toggle a program Online by inputting either "Y" or "N". That column drives a lot of different calculations throughout the workbook. Solver runs correctly if I leave this column out and manually turn programs on or off. However, I would like to get to the point where Solver will change the values in that column from "Y" to "N" or vice versa. Is this possible to do or do the variables have to be numbers?

    Thank you!
    Attached Images Attached Images

  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,758

    Re: Using Solver to change text value

    I think you stand a better chance of getting help if you upload a file with your solver model. Solver works only with numbers but you can name a range "CellsToChange" and solver will accept as an input to the solver setup because of the numbers "found" beneath the label.

    In decisions model with solver binaries are often used in order to decide what actions should be taken so getting 1 and 0 from solver you could easily "translate" that to "Y" and "N".

    Alf
    Last edited by Alf; 10-16-2019 at 05:39 PM.

  3. #3
    Forum Contributor
    Join Date
    06-27-2013
    Location
    Nebraska
    MS-Off Ver
    Office 365
    Posts
    115

    Re: Using Solver to change text value

    Hello Alf,

    I was able to recreate a sheet without all of the "extra" stuff going on in the background. I tried changing "Online" column to be driven by a formula with an IF statement that looks at I11:I16. Those values are supposed to binary and changed by the solver. The problem I am having is they are not staying 1 or 0 when I run the solver.

    Thank you
    Attached Files Attached Files

  4. #4
    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: Using Solver to change text value

    Just had a quick look at you uploaded file but as I'm off to Stockholm tomorrow I've not got much time to spend at the moment. Will have a go at it during the weekend. When I tried to run your present setup solver stopped after a few seconds telling me there was an error in the target cell (#DIV/0!).

    Just clearing range B4:B7 and I11:I16 gave me a "DIV/0!" error as well since the range I11:I16 is part of the calculation in the value of the target cell.

    So as a last resort I tested running your model using the "Evolutionary" solver engine and it did not complain about #DIV/0! error and found a solution after 10 - 15 seconds. How good this solution is or if it makes any sense to you is for you to decide as I need a bit of time in order to understand you model.

    I'm not familiar with the mathematics behind the "Evolutionary" solver engine but I'll add a link from the makers of solver where they do a bit of explaining.

    https://www.solver.com/excel-solver-...ing-conditions

    Alf
    Last edited by Alf; 10-17-2019 at 01:48 PM.

  5. #5
    Forum Contributor
    Join Date
    06-27-2013
    Location
    Nebraska
    MS-Off Ver
    Office 365
    Posts
    115

    Re: Using Solver to change text value

    I noticed the DIV/0 errors after posting the last file. I have gone in and updated the formulas with IFERROR() statements to keep that from hanging up the solver. Depending on the inputs I have set when I start running the solver I can get it to run properly, however more often than not the solution it comes up with are not satisfying the constraints for 0<B30<=1, 0<C30<=1, and 0<D30<=1. The reason for this constraint is those cells show what capacity of each machine is used. If that number is greater than 1 then it would require a machine to do more work than possible in a given shift of work.

    I have uploaded the new file with the updated formulas. Thank you for the link, it was helpful to further understand what is going on when the Solver is looking for a solution.
    Attached Files Attached Files
    Last edited by erice; 10-17-2019 at 02:26 PM.

  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: Using Solver to change text value

    Not sure if you tested the Evolutionary engine for solver so I only changed solver engine and did a rerun with Evolutionary engine.

    Got a lower value for the target cell, again you need to check if the solution makes sense. Checking B30, C30 and D30 and these values are between 0 and 1.

    Alf
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    06-27-2013
    Location
    Nebraska
    MS-Off Ver
    Office 365
    Posts
    115

    Re: Using Solver to change text value

    I did test out the evolutionary method in my first file and still had issues with getting answers outside of the parameters set. For the most recent version with the IFERROR() statements it seems to be working correctly. Your file you attached was yielding acceptable results. Thank you for helping out with this!

  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: Using Solver to change text value

    You are welcome and thanks for feedback

    Are you happy with the last results or should I try to see if I could make a better contribution to you present model? Not sure I can but I could always have a go at it during the weekend.
    Then there is always the chance some other forum member finds this thread of interest and have some ideas on how to modify you model.

    I'll also test if "OpenSolve" can work with your model.

    Alf

  9. #9
    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: Using Solver to change text value

    So I got a bit of time as train to Stockholm arrived on schedule.

    I've modified your model by only using binaries in range C11:C16. The advantages with this was I got rid of a number of If constraints that solver is not happy with.

    You got some massive If constraints in range B26:D26 so I substituted all these formulas with the "SUMPRODUCT" function i.e. B26 formula is
    Please Login or Register  to view this content.
    and since the "Online" range is set as binarier you will only get a value if the "Online" value is 1, for C26 and D26 has a similar formula only a different maskine range.

    I also set a constraint for the range B30:D30 to be equal to or grater than 0 and equal to or less than 1. Not sure if it's really needed.

    Alf
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    06-27-2013
    Location
    Nebraska
    MS-Off Ver
    Office 365
    Posts
    115

    Re: Using Solver to change text value

    Thank you for looking at this more in depth. The results seem to be correct and the constraints are all satisfied. I completely forgot about using SUMPRODUCT... That helped a lot to clean things up, not to mention the faster computing time by being able to use nonlinear compared to the evolutionary solver. I'm now going through my original workbook to get these functions added to it.

    Thanks again!
    Last edited by erice; 10-18-2019 at 03:21 PM.

  11. #11
    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: Using Solver to change text value

    You are welcome and I do enjoy getting feedback a thing that does not happen to often nowadays. Must say I also was impressed over the speed increase the modification of the model made.

    I guess that setting up a proper model is the most important thing with a complex solver model, most of the time one know what to aim for but how to model that in the best way is often the problem. Well at least this problem is solved to the satisfaction of you and me.

    So if you ever come to Stockholm (I recommend in the summer time) you will have to treat me to a cup of coffee at Nytorget where I can sit enjoying the coffee and you may sit ogling the girls as Sweden is known as "The Land of the Midnight Sin". Don't ask me anything about that because:

    A: I'm a Norwegian
    B; My wife don't think I should sit ogling young girls

    Regards

    Alf

  12. #12
    Valued Forum Contributor Hydraulics's Avatar
    Join Date
    07-15-2018
    Location
    Udine - Italy
    MS-Off Ver
    Office 365
    Posts
    369

    Re: Using Solver to change text value

    I had a look at Alf's setup and there isn't much to add, well done!

    The objective function, however, puzzles me. It seems to me there is a "Shifts" not necessary in the formula

    Please Login or Register  to view this content.
    because the term

    Please Login or Register  to view this content.
    already includes the number of shifts, as in

    Please Login or Register  to view this content.
    Substituting, the OF in E4 would be

    Please Login or Register  to view this content.
    It may not change the result for this specific instance of the model, but you should check that this is really what you want to minimize.

    HTH,

    Francesco
    Aim high or don't even try.
    ---------------------------------
    If your question has been answered, don't forget to mark the thread as SOLVED.
    If you find an answer helpful, click on the star icon at the bottom of the post.

  13. #13
    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: Using Solver to change text value

    Hi Francesco

    Thank for comment

    One other thing I did was to set a constraint
    Please Login or Register  to view this content.
    as I missed that erice had sett the same constraint in a slightly different way i.e.
    Please Login or Register  to view this content.
    so his code is much more readable and I'm sure this will be counted as 6 constraint while mine looks to be only 2 but I have a sneaky suspicion that it in reality it is 3*2 i.e. 6 as each constraint contains 3 cell addresses. But I would really like to know if this is so as well as if this makes any difference in the calculation time? Hardly likely I think but would be nice to know for sure.

    Alf

  14. #14
    Valued Forum Contributor Hydraulics's Avatar
    Join Date
    07-15-2018
    Location
    Udine - Italy
    MS-Off Ver
    Office 365
    Posts
    369

    Re: Using Solver to change text value

    Quote Originally Posted by Alf View Post
    [...]I have a sneaky suspicion that it in reality it is 3*2 i.e. 6 as each constraint contains 3 cell addresses. But I would really like to know if this is so as well as if this makes any difference in the calculation time?
    Hi Alf,

    I don't know Solver internals but I'm pretty sure that the number of costraints is exactly the same, no matter how they are written. See for instance

    https://www.solver.com/excel-solver-...ete-constraint

    point 4.

    (Btw, if that wasn't so, you could easily fool Excel and violate the limit on the number of costraints simply expressing them in a compact form.)

    And I would bet that solving time remains the same.

    HTH,

    Francesco

  15. #15
    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: Using Solver to change text value

    Yes that sounds reasonable.

    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: 2
    Last Post: 08-23-2017, 11:56 AM
  2. [SOLVED] SOLVER does not change inputs!
    By brunojacko in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-08-2017, 03:47 PM
  3. Change solver variables in vba
    By blimes in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-11-2016, 01:44 PM
  4. Need urgent help with solver. It does not change value
    By mikelowrey36 in forum Excel General
    Replies: 1
    Last Post: 04-09-2013, 08:09 AM
  5. Excel 2007 : Solver does not change values
    By RGrahamB in forum Excel General
    Replies: 3
    Last Post: 11-30-2010, 10:24 PM
  6. How do I change a password for VB SOLVER Add-in?
    By Jim Shaffer in forum Excel General
    Replies: 1
    Last Post: 04-12-2006, 12:10 AM
  7. [SOLVED] Re-run solver when some inputs change
    By Bojana in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-04-2005, 09:05 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