Solver Constraint If statement workaround

I am developing a production simulation using OpenSolver in Excel. I know that I cannot use if, or, and statements to constrain a solver.

I need a constraint that says:

X has to be within the range of 150 - 250, or X can equal 0.

I have read about different ways to substitute binary constraints for conditional formulas, but I am not sure how to make it work exactly.

Any help is greatly appreciated.

Try this (A1 = X) ...

=IF(OR(A1=0,AND(A1>=150,A1<=250)),"Yes","No")

Originally Posted by Phuocam

Try this (A1 = X) ...

=IF(OR(A1=0,AND(A1>=150,A1<=250)),"Yes","No")

Excel Solver does not work with If functions because they are not continuous. I need a constraint(s) that is compatible with solver

OpenSolver hmm, then I assume your problem is linear and the size of it is greater than the excel solver can handle.

As I've not seen your model my suggestion on a possible way to handle this is based on a "guessimate."

I would probably set the constraint as x >= 0 and x <= 250, rund the simulation and see if there is an optimal solution where x = 0. If there is none then I would forget about setting a constraint where x = 0 and just use x >= 150 and x <= 250 for the solver run.

If there are some wher x = 0 or x close to zero I would set x to zero and separate these as a special case.

Alf

Second thoughts:

As x can either be zero or a value in the range 150 to 250 you could run solver using a macro setting x >= 150 and <= 250 and copy the result to a specified range. Then the macro deletes the previous x constraint and add the constraint x = 0. Copy this result to another part of the spreadsheet and see which is the better solution.

Alf

Originally Posted by Alf

Second thoughts:

As x can either be zero or a value in the range 150 to 250 you could run solver using a macro setting x >= 150 and <= 250 and copy the result to a specified range. Then the macro deletes the previous x constraint and add the constraint x = 0. Copy this result to another part of the spreadsheet and see which is the better solution.

Alf

This would probably work, however I am trying to develop a model in which x, y, z all have similar constraints. They are three production machines that each have a min and max production level if they are running, but I want the solver to tell me which machines to run.

I have tried to create a constraint saying:

150 d <= x <= 250d

d is a binary constraint. X and d are both decision variables, but I cannot seem to make it work.

I've set up a solver model that you could find usefull. To test run macro "SolvLoop"

Before you can run macro you need to set a refernce to the excel solver in "Visual Basic". Click on "Visual Basic" icon, then "Tools" and finaly "References" and then tick box marked "Solver".

The model is cost driven i.e. there is a starting cost for each machine + a piece cost for each piece produced. You set you the production (units needed) in cell E19 and the run macro.

The command for running "OpenSolver" in a macro is I believe.

Please Login or Register  to view this content.

Myself I prefer to build my models in the excel environment and then run "OpenSolver"

Alf

Thank you very much for the help. The macro works great and runs.

I probably should have been more specific at the start. My solver is intended to schedule production for 10+ periods, so I need somehow to apply the start cost so that if a machine is running in day 1 and day 2 there is no penalty. But if the machine is running in day 1 and not running in day 2 there is a penalty.

I have attached an example spreadsheet with the solver built in to see if it helps.

Thanks for feedback and rep

Looking at your spreadsheet it seems like the target cell is L19 (set to min) but as there is no formula in this cell I'm a bit unsure what you are trying to acchieve.

Minimizing costs probably by minimizing stock level and production costs. Looking at your file prodution cost per unit is 40 for M1, 30 for M2 and 20 for M3?

The inventory row is a bit puzzeling to me. You start with an opening stock of 5000 on day 1, there is no production and you got a demand of 1500 so I would say the inventory in cell D46 should be 3500. And on Day 2 you have a demand of 3000 and a production of 1240 so when all is said and done your stock is 1740 and this is below the 2000 min limit.

On day 3 you demand is 2500, production is 1260 so you stock goes down to 500.

What is the cost of "over production", if start/stop cost per engine is 26000 and production cost for M1 is 40 then the 26000 is equal to over production of 650 units on M1, with costs 30/unit on M2 over production of 867 units is equal to 26000 and 1300 units on M3 where production costs is 20/unit.

Just some of my first thoughts looking at your file.

Alf

The model should be set to minimize C10, I have made the adjustments in my sheet. I was messing around trying different things and forgot to change it back. Apologies.

I am trying to minimize total cost, but I need to include minimizing shutdown costs (which is not included in my current solver). I want to build inventory before I shut a machine down.

You are correct about the inventory line. I had just plugged random demand levels in attempt to make the solver work, but I have changed the numbers now so it makes sense.



I did not think I needed a cost of overproduction, or that it would work to define it that way because the $26,000 is not a daily fixed cost to run. I only want the solver to add $26000 at the day a machine shuts down, and $26000 the day a kiln starts back up. Although really this does not matter if the costs are applied to starting the machine, stopping the machine, or both, it will all achieve what I want.

I think you have solved your problem with your last setup.

I did make some minor changes, set range D14:J16 to integer and changed the excel default setup from "GRG Nonlinear" to "Simplex LP" and changed "Calculation Option" from "Manual" to "Automatic"

Looking at the result I could see a need for a small manual "adjustment" i.e. Solver shut down M2 on day 6 and started it on day 7 in order to minimize stock. In real life M2 should of course be kept running on day 6 and shut down on day 7.

What would be nice is to have is a "constraint" that makes it an advantage to keep a kiln running for as long as possible before a shutdown is scheduled but abnormalities like this should be easy to find so it's simple to do a manual adjustment.

Alf

Thank you very much for the help with all of this. The solver works in the way that you fixed it. I am planning to develop a constraint that keeps a machine running for as long as possible.

I was able to achieve the same result as you by writing a constraint that says

if d_t + d_t-1 = 1 then z_t = 1, otherwise z_t = 0
where t is the day index, d_t is the binary variable that shows if the machine is open or not in day t, and z_t is the newly introduced variable, that shows if there is a switch between days t-1 and t.

To enforce this logic, you will need the following constraints:

z_t <= d_t + d_t-1
z_t >= d_t - d_t-1
z_t >= d_t-1 - d_t
Why this works

Here is the set of all possible values of d_t, d_t-1 and z_t, as they result from the constraint above:

d_t | d_t-1 | z_t
--------------------------
0 | 0 | 0
0 | 1 | 1
1 | 0 | 1
1 | 1 | {0, 1}
Note than in the last case, when d_t and d_t-1 are both 1, the constraints do not enforce z_t to a particular value. However, since you will have $26,000*z_t in your objective function, an optimal solution will pick up z_t = 0 when it has the flexibility to do so, because it is cheaper than z_t=1.

Perhaps there are easier ways to do it. This way works for what I want it to do.

The only thing I need to figure out is how to encourage a machine to run as long as possible before shutting down, like you mentioned in your previous post.

Also I know the External Purchase option does not need to be apart of the binary grid, it can just be a standard min/max constraint. I have since fixed it, just in a different version.

Can Anybody Help me with same case, In my case I dont need this to changed using VBA because I have a array of Output which has 120 Cells