Search code examples
excelschedulingproductionsolver

How to implement if statement to Excel Solver

I want a variable to be either 0 or greater than 3 in excel on my production planning design with solver. Is this possible?

I have tried using IF statements as in if x is not zero then its going to be subject to =>3 constraint otherwise there is going to be a random big number on the LHS of the constraint to avoid it. This method gave an infeasable solution.

Thanks in advance!

Solution

  • This is called a semi-continuous variable. You need an extra binary variable to indicate if x is zero or not. E.g.:

    δ = 1 if x≥3
δ = 0 if x=0

    This can be implemented as:

    3δ ≤ x ≤ 1000δ

    In Excel these become two different constraints (3δ -x ≤ 0 and x - 1000δ ≤ 0).

    More advanced solvers have built-in facilities to deal with semi-continuous variables.