+ Reply to Thread
Results 1 to 4 of 4

Can Solver do this? Optimizing several binary criteria

  1. #1
    Registered User
    Join Date
    08-24-2021
    Location
    Washington, DC
    MS-Off Ver
    Student 2007
    Posts
    2

    Can Solver do this? Optimizing several binary criteria

    I am trying to solve for optimization through a series of binary criteria, and was thinking that maybe Solver could help? I can't wrap my head around how to make it work though. Maybe Solver isn't even the answer, I'm not sure :/

    Capture.PNG
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: Can Solver do this? Optimizing several binary criteria

    First let me comment on "ratio".

    1:0 would be the same as 2:0 - both leading to infinity

    But let's focus on difference rather than mathematical ratio

    Shall 2:0 be treated as equaly good solution as 3:1 ? I assumed that 2:0 is better than 3:1 so in final goal setting (maximizing) i used
    =C17-1,01*D17
    where in row 17 are taken into account positive and negative results (meeting constraints)

    As variable in solver I used integers -1;0;1 -1 meaning given constrain being ignored, 0 set to 0 and 1 set to 1.

    Solver shall find one of best solutions either B,E,F for constr3=0 (and other not taken into account) or both constr3 and constr4 = 0 (and other not taken into account)

    See attached. Solver Engine is set to evolutionary (I don't remember, but I think it could be already available in Excel 2007).
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    08-24-2021
    Location
    Washington, DC
    MS-Off Ver
    Student 2007
    Posts
    2

    Re: Can Solver do this? Optimizing several binary criteria

    Ah nice work! Suuuper smart way of doing this, very nice!

    You're right about the ratio thing too of course haha, and yes what you have provided is exactly what I was trying to accomplish.

    I can't seem to get it to provide the most optimal solution on my older version of excel (perhaps because there is no option to enable an evolutionary mode), so I will have to figure something out, but that is beyond the scope of what can be helped with here haha.

    Thanks and good work!

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: Can Solver do this? Optimizing several binary criteria

    Of course with this number of constraints you can use a brute force approach and list all posiibilities. it's 3^4 - 1 so just 80:

    0 0 0 0
    1 0 0 0
    -1 0 0 0
    0 1 0 0
    1 1 0 0
    -1 1 0 0
    0 -1 0 0
    1 -1 0 0
    etc.
    See columns N:Q in sheet2.
    Each cell N3:Q3 has similar but different formula. These formulas are copied down to list all 80 possibilities.
    Then calculations from J3:J9 are made in columns R:X. It is one formula in R3 copied right and down. (I used OFFSET to use data from respective number of rows down)
    What was calculated in C17:D17 and C19 is now in Y3:AA3 and copied down (here I had to use TRANSPOSE to made both arrays passed to SUMPRODUCT oriented the same way)

    Conditional formatting is used to highlight best results (as previously noted -1 -1 0 0 and -1 -1 -1 0 give best results - 3 positive and 0 negative.

    So we found best solutions without using solver in this sheet
    Attached Files Attached Files
    Last edited by Kaper; 08-24-2021 at 05:40 PM.

+ 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. Solver returns non binary answer in binary constrained cells
    By Tushar Mehta in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-07-2005, 12:05 AM
  2. [SOLVED] Solver returns non binary answer in binary constrained cells
    By Navy Student in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  3. [SOLVED] Solver returns non binary answer in binary constrained cells
    By Navy Student in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  4. Solver returns non binary answer in binary constrained cells
    By Tushar Mehta in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 09:05 AM
  5. [SOLVED] Solver returns non binary answer in binary constrained cells
    By Tushar Mehta in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 08:05 AM
  6. Solver returns non binary answer in binary constrained cells
    By Navy Student in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  7. [SOLVED] Solver returns non binary answer in binary constrained cells
    By Navy Student in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  8. [SOLVED] Solver returns non binary answer in binary constrained cells
    By Navy Student in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-01-2005, 11:05 AM

Tags for this Thread

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