Missing AllDifferent constraint in Excel 2007

Question

I am trying to use the solver in Excel 2007 but I did not find "alldifferent" constraint in it, e.g:

example

I can see "integer" "binary" but there is no "alldifferent".

Can someone help me find it? Thanks!

Answer 1

The alldifferent constraint was added in Excel 2010.

You can impose alldifferent constraints by using additional formulae and constraints in Excel 2007. Below is a simplified example ...

A screenshot of the setup ...

Here, Eqn1 is =A2*10+A3*9+A4*8+A5*7+A6*6 and Eqn2 is =A6*10+A5*9+A4*8+A3*7+A2*6

You can see that Cell B2 contains the CSE equation {=MIN(ABS(A2-A3:$A$6))}. This is dragged down to B5, where the equation is {=MIN(ABS(A5-A6:$A$6))}.

As an example, Solver is setup with:

• SetObjective: $F$2
• To: Min
• By Changing: $A$2:$A$6
• Subject to the constraints:
  • $A$2:$A$6 <= $D$2:$D$6
  • $A$2:$A$6 >= $C$2:$C$6
  • $B$2:$B$5 >= $E$2:$E$5
  • $A$2:$A$6 = integer
• Select a Solving Method: Evolutionary

Below is a screenshot ...

Below is a screenshot of the solution.

It found the expected answer of 1,2,3,4,5.

Changing SetObjective to $G$2 gets the answer 5,4,3,2,1.