Given the interchange constraint, what you are really asking is: Among all the reorderings of a set of values, which permutation of those values minimizes the target function?
This problem can be structured so that Solver can find a solution. The key is to use the ordering of the values, rather than the values themselves, as the quantities Solver varies in order to find the minimum value of the target function. Solver has a built-in mechanism to permute those values.
The figure below shows the worksheet setup for an illustrative example.
Cells A4:A7
hold the values that need to be reordered to find a minimum.
Cells C4:C7
hold an initial row ordering for those values - the values in C4:C7
are those which will be varied by Solver.
The formulas in E4:E7
look up the values in A4:A7
that correspond to the row order in C4:C7
.
Cell C9 holds the formula that will be minimized in my example - note that the formula depends on the values in E4:E7
, not on those in A4:A7
.
Next is the Solver settings for the problem. Here, you need to set the constraint for cells C4:C7
to AllDifferent
and the Solving Method to Evolutionary
.
To set the constraint for C4:C7
to AllDifferent
, choose the option dif
from the dropdown on the Add Constraint dialogue box. (See this link for a helpful discussion of the special constraint options available in Solver.)
I did not find it necessary to fiddle with the optional settings for the solving method by, for example, putting a time limit or an iteration limit on the solution search. With just four variable cells and a very simple target function, Solver found a solution in only a few seconds. The row ordering that produced the minimum solution was shown in C4:C7
, and the order of values in E4:E7
.