# How to reuse existing values when using Solver in MS Excel 2010

I need to design a process layout in Excel using Solver. I have a column of values, that I have assigned as "By changing cells", and which should be changed in order to find the min result. The problem is, instead of changing these values, I need to switch them places, till I find the best combination.

How to specify in Solver, or with some Excel magic, that I need values interchanged, but not overwritten, as such approach always returns 0's everywhere?

Maybe there is a mathematically sound constraint I could use, that would prevent those values from changing?

-

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`.

-