Suppose I have an $1 \times 10$ array of discrete binary decision variables of price points, where:
- $0$ = do not use this price point
- $1$ = use this price point
and that any number of price points can be selected. For example, $[1, 0, 0, 1, 0, 0, 1, 0, 0, 1]$ means that $1$, $4$, $7$, and $10$ are selected.
How do I add a constraint in Excel Solver such that the distance between the largest and smallest price points is less than or equal to cell I enter?
For instance, if I enter in $5$ in the "Maximum Price Spread" cell:
A: [1, 0, 0, 1, 0, 0, 1, 0, 0, 1] violates constraint
B: [0, 0, 1, 0, 0, 0, 1, 1, 0, 0] meets constraint
I would need to construct a constraint in Solver such that [A] cannot happen because the distance between $1$ and $10$ is not $\leq 5$ maximum spread.
The decision array [B] would meet the constraint since the highest value, $8$, less the lowest value, $3 = 5 \leq 5$ maximum spread.
The problem is stumping me because the constraint needs to be set up whilst remaining linear. For example, MAX() and MIN() cannot be used to evaluate changing cells values because it would result in a discontinuous function.
You can add the following constraint: $$ \max_{i,j} \; |ix_i - jx_j| \le \delta $$ where $x_i$ are your binaries and $\delta$ your limit. Of course, you need to linearize this constraint, for example as follows: \begin{align} &z \le \delta \\ &z \ge ix_i - jx_j \quad \forall i,j \\ &z \ge jx_j - ix_i \quad \forall i,j \end{align}