+ Reply to Thread
Results 1 to 4 of 4

Optimization problem when Solver doesn't work

  1. #1
    Registered User
    Join Date
    05-23-2012
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    2

    Optimization problem when Solver doesn't work

    I have a problem which I tried to use with solver but it doesn't give me a right answer and I was wondering how and if it's possible to right a program to do what I need.
    I'll give a simple version of what the problem is and maybe somebody can help out.
    I have a column (lets say A1:A10) which I want to have binary values (zeros or ones).
    I have another column (lets say B1:B10) that take values from a complicated formula depending on where the (zeros and ones) values are on column A.
    I have a third column (lets say C1:C10) that have some values that don't change.
    What I want to succeed is to create a program that depending on how many values of "1" I want in column A to find the optimal pattern that would maximize the sumproduct(B1:B10,C1:C10)
    What I tried to do is put solver to find the max of a cell that I had the sumproduct equation, by changing A1:A10 with two constraints. The first is that A1:A10 is binary and the second is that the SUM(A1:A10) is equal to the value that I want. It works mechanically but doesn't give the best answer (I've tested different solver algorithms but I had no luck)... It seems the result uses the last options (e.g. if i choose 3 values of 1, they always appear in C8:C10...)
    Is there a way to overcome this with VBA code? Thank you in advance

  2. #2
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,532

    Re: Optimization problem when Solver doesn't work

    I have another column (lets say B1:B10) that take values from a complicated formula depending on where the (zeros and ones) values are on column A.
    If you are using an IF statement in your formula i.e. "=IF(A2=1,B2=5,2) this will not work with solver as solve can't handle discontinuous functions like "IF", "COUNTIF" and "SUMIF"

    Perhaps you could upload a sample workbook.

    Alf

  3. #3
    Registered User
    Join Date
    05-23-2012
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Optimization problem when Solver doesn't work

    Thank you for the answer so far. Yes there are indeed some ifs involved in the equations which clears up why solver doesn't work. As you requested I'm attaching a simplified version of it. Basically I'm trying to maximize cell "O63". This is supposed to be done by changing values S4:S62 between "0" and "1". In the example file the sum of them (S63) is equal to 10. I would like for their sum to be equal to S65 (i.e. 15). So the final problem is which 15 cells amongst S4:S62 that should take the value "1" in order to get the maximum value for cell O63
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,532

    Re: Optimization problem when Solver doesn't work

    Looking at your formula in cell J4
    Please Login or Register  to view this content.
    I would change that to

    Please Login or Register  to view this content.
    so if you multiply the VLOOKUP part with the corresponding S value you either get 0 or the VLOOKUP
    value depending on if the S value is 0 or 1.

    Alf

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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