How to use "or" in Excel Solver constraint

Question

I am adding a constraint to Excel Solver.

The constraint is saying an input data at location A3 must be either 0 or between 3 and 8.

How could I add this constraint in Excel Solver since I could not find 'or' function in solver constraint box. It only has ">=", "<=", "=", "int", "bin", "dif" operators there.

Many Thanks

Answer 1

This is a little bit of a hack, but you could create a function that is zero when your constraints are met, and nonzero when they are not. For example, if you have (in named ranges) a number of individual values val_1, val_2 etc. that are all valid, and a range llim and ulim (lower and upper limit), then the following equation will evaluate to zero if your conditions are met:

=(A3 - val_1) * (A3 - val_2) * FLOOR.PRECISE(ABS((A3 - (llim + ulim) / 2) / ((ulim - llim) / 2)))

When cell A3 is either val_1 or val_2, you will multiply your expression by zero; and when A3 is between llim and ulim, the expression inside the FLOOR.PRECISE() function will evaluate to something smaller than 1 - so the FLOOR will be zero.

Enter that expression in a cell, and make your constraint that this cell must be zero... It ought to work. It did for me - with the function to optimize being "3 * A3", and limits set to 3 and 8, the solution was 7.99999.

Note - one problem with this is that you will most likely get the solver "stuck" in just one interval - it will never notice the other possible values. If that is important, you may have to come up with a transformation of a continuous variable into one that has discrete values plus a range. Example:

=IF(A3<-1,val_1,IF(A3<0,val_2,llim+(ulim-llim)*ATAN(A3)*2/PI()))

Now A3 can vary over the full range, and yet the cell with this formula in it will always have a "valid" value in it. Again, if you need more fixed values you can add more nested IF statements...