Lock/Exclude in Binary Constraint

laxin

New Member
Joined
Apr 13, 2015
Messages
10
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!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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")
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:

BinaryNameOutputLock/ExcludePrice
aL200
b250
c300
d400
eX375
f310
g275

<tbody>
</tbody>

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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top