Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

initial worksheet setup

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.


Solver settings


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

dif constraint setting

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.

Solver solution

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.