+ Reply to Thread
Results 1 to 9 of 9

Excel Solver - 2 Variables , Whole Numbers

  1. #1
    Registered User
    Join Date
    09-29-2016
    Location
    london
    MS-Off Ver
    2016
    Posts
    19

    Excel Solver - 2 Variables , Whole Numbers

    Hi

    I am trying to get the MAX number, by changing 2 variable cells.

    I have tried to limit to INTIGER but excel still gives me values with decimal points, I am also not sure if I am using the right solving method in solver.

    Any help would be much appreciated

    Max value achievable in E5

    CHANGING CELLS B1 AND B2

    B1 AND B2 HAVE TO BE WHOLE NUMBERS AND ABOVE 1

    B2 HAS TO BE GREATER THAN B1

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Excel Solver - 2 Variables , Whole Numbers

    What's wrong with the solution of 6?

  3. #3
    Registered User
    Join Date
    09-29-2016
    Location
    london
    MS-Off Ver
    2016
    Posts
    19

    Re: Excel Solver - 2 Variables , Whole Numbers

    If you change B1 and B2 to 2 and 4 manually you get 35 in E5 which is clearly bigger than 6

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

    Re: Excel Solver - 2 Variables , Whole Numbers

    Do you believe that 2 4 is the best final answer? I found that 1 8 gives a 37.

    What other constraints do you have? How familiar are you with the objective function? Are you required to use Solver for this?

    I manually tried a "brute force" "try every possible combination of inputs between 1 and 10" kind of algorithm. It seems that, if B2 is above about 8, the OF is always 0, so I did not go much beyond that. If you know your OF (e5) well enough to confidently state that the solution will never be above ____, then a brute force algorithm like this might be easier, faster, and more reliable than Solver's algorithms.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    09-29-2016
    Location
    london
    MS-Off Ver
    2016
    Posts
    19

    Re: Excel Solver - 2 Variables , Whole Numbers

    HI

    You are right re 37.

    I don't know anything about the objective function or brute force in excel.

    having thought through this more and based on your post I would say

    B1 AND B2 can be any number between 2 and 10

    B1 CAN BE BIGGER THAN B2 AND VICE VERSA

    how would I use the brute force method to check each combination in an automated fashion?

    Thanks

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

    Re: Excel Solver - 2 Variables , Whole Numbers

    I don't know anything about the objective function
    You have to know something about the OF since you programmed the function into the spreadsheet. I generally find it difficult to program any kind of optimization algorithm if I do not have a good understanding of the objective function and its behavior. For example, we have rejected all solutions outside of the 2 to 10 range -- is that rejection based on sound understanding of the behavior of the OF?

    how would I use the brute force method to check each combination in an automated fashion?
    I'm sure there are multiple ways. Here's how I would probably do it.
    1) Go through all of your formulas and adjust the relative and absolute referencing as needed to make sure everything will copy well.
    2) Copy the block of cells across as many times as needed
    3) Insert your starting values (2 2) into the first "iteration" (B1:B2). Then, have formulas in subsequent copies (H1:H2 if that is where they copied to) that will generate the next combination from the previous. Something =if(b1+1>10,1,b1+1) in H1 and =if(b1+1>10,b2+1,b2) in H2 then copy across.
    4) Then final analysis functions (=max(A5:AAA5)) somewhere to tell me what the maximum is and where it is at [=match(referencetomax,A5:AAA5,0)], and so on.

  7. #7
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Excel Solver - 2 Variables , Whole Numbers

    MrShorty - out of interest, do you know why Solver doesn't work? Are there too many possibilities so that it gives up without trying.

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

    Re: Excel Solver - 2 Variables , Whole Numbers

    I don't really understand the algorithms Solver uses (especially when you get into these linear integer programming problems where you specify integer constraints), but I don't think it is a question of Solver "giving up" because there are too many possibilities. It might also depend on what Solver algorithm you are using -- GRG non-linear, Simples, or Evolutionary.

    If you are interested in researching this, you might put something like "linear integer programming with Excel's Solver" into your favorite internet search engine. I know that there are tutorials and other discussions out there that discuss these kinds of problems, and how to best formulate your objective function and other aspects of the problem to best help Solver find the solution.

  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: Excel Solver - 2 Variables , Whole Numbers

    It seems like the values are oscillating.

    Osilating.jpg

    The first run of Solver will set B1 to 2 and B2 to 3 and finding a E5 value of 6. Setting a constraint i.e. E5 >= 7 will force Solver to find a new value of E5 = 10 with B1 = 1 and B2 = 2 but this is as far as Solver will go. Changing the E5 constraint to >=11 will make Solver go infeasible.

    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. Need help with Rounding numbers with no decimals using Excel 2007 Solver
    By lester.ilao in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 03-21-2012, 11:03 PM
  2. excel solver binary variables
    By kingkong123 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-28-2011, 01:59 PM
  3. Finding a minimum with solver and 3 variables
    By BCITgirl in forum Excel General
    Replies: 2
    Last Post: 12-05-2010, 05:16 AM
  4. using variables in excel solver constraints problem!
    By 77anders in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-12-2009, 08:49 PM
  5. Replies: 1
    Last Post: 08-08-2006, 01:00 PM
  6. Set solver constraints so variables are either 1 OR 0.
    By Gwyndalf in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-13-2006, 08:20 PM
  7. variables in excel solver for vba
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-16-2006, 10:50 AM
  8. solver and defining all variables different than one another
    By excel_excel_excel in forum Excel General
    Replies: 0
    Last Post: 07-19-2005, 03: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