Results 1 to 8 of 8

Thread: Lock/Exclude in Binary Constraint
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2015
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Lock/Exclude in Binary Constraint

    I am using the binary constraint for an optimization tool. Currently am trying to include a feature that can either lock in a certain row during the optimization or exclude it by either putting an L or an X in a column that will correspond to the set of data within its row. Essentially, if their is an L, the price corresponding to that row will be optimized around, and if their is an X, it will be excluded from the optimization.

    Im still new to solver and I don't really know where to start here. If anyone can help me out or point me in the right direction, it would be greatly appreciated!

  2. #2
    New Member
    Join Date
    Apr 2015
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lock/Exclude in Binary Constraint

    bump... help?

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lock/Exclude in Binary Constraint

    From here you can see that a binary constraint is one that can either take a 1 or a 0. So, you can keep your L / X, but use them in a separate cell. In other words, you need two cells: One, say A1, that holds the binary constraint (1 or 0), and in B1, a formula like

    =IF(A1=1,"L","X")
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  4. #4
    New Member
    Join Date
    Apr 2015
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lock/Exclude in Binary Constraint

    Quote Originally Posted by Juan Pablo González View Post
    From here you can see that a binary constraint is one that can either take a 1 or a 0. So, you can keep your L / X, but use them in a separate cell. In other words, you need two cells: One, say A1, that holds the binary constraint (1 or 0), and in B1, a formula like

    =IF(A1=1,"L","X")
    Will that be factored in by solver during the operation? Thats what I need ... Im not sure its that simple, but Im no expert

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lock/Exclude in Binary Constraint

    Quote Originally Posted by laxin View Post
    Will that be factored in by solver during the operation? Thats what I need ... Im not sure its that simple, but Im no expert
    Yes, if you set A1 to be a binary constraint, it will be used by Solver, and if your model depends on B1, then the formulas will autocalculate and Solver will see that.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  6. #6
    New Member
    Join Date
    Apr 2015
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lock/Exclude in Binary Constraint

    Quote Originally Posted by Juan Pablo González View Post
    From here you can see that a binary constraint is one that can either take a 1 or a 0. So, you can keep your L / X, but use them in a separate cell. In other words, you need two cells: One, say A1, that holds the binary constraint (1 or 0), and in B1, a formula like

    =IF(A1=1,"L","X")
    The L or the X notation doesnt matter. I can use 1 and 0 too. I just need the solver/optimization to read the column with whatever on/off, 1/0, true/false, lock/exclude notation to either lock the row within the optimization or exclude it entirely.

    My true problem is essentially this:

    Binary Name Output Lock/Exclude Price
    a L 200
    b 250
    c 300
    d 400
    e X 375
    f 310
    g 275

    max price is, say, 800. I want to maximize the output within the max price. In column A, there will be a 0 if the person is not included in the optimized result, and 1 if they are.

    The program runs to this point. I am trying to add a feature where I can lock/exclude a person from the optimization. I have not been able to figure this out yet

  7. #7
    New Member
    Join Date
    Apr 2015
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lock/Exclude in Binary Constraint

    Bump, still need help if anyone can

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lock/Exclude in Binary Constraint

    If I understand you correctly, in the "Output" column, which starts in C2, I put the following formula:

    Code:
    =E2*IF(D2="L",1,IF(D2="X",0,A2))
    Then in C9 I put the total of the column

    Code:
    =SUM(C2:C8)
    I also put in C11 the "Max Price" allowed, 800.

    Now, I defined the solver model as follows:

    Set Objective: C9
    To: MAX

    Constraints:
    A2:A8 = binary
    C9 <= C11
    Regards,

    Juan Pablo González
    http://www.juanpg.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •