Question

using LP excel solver

objective: i need to minimize the distance each team has to travel

the distance is represented by the yellow squares

(so for example for KU to travel to MW is 488 miles)

constraints

each place needs to have a #1, #2, #3 and #4 seed

(there cannot be any repeated seeds in any of the 4 places  )

place SOUTH MW EAST WEST TEAM SEED 1 1 1 1 488 838 104 1981 527 662 149 1319 1417 1910 1134 757 185 937 2339 795 664 993 1301 1131 1434 541 2581 1146 1097 708 2430 2 1302 1996 2 2 2 1897 1787 89 KU Baylor Dayton Gonzaga Creighton Florida St. Kentucky SDSU Duke MSU Villanova Seton Hall Wisconsin Oregon Maryland Louisville 3 474 421 558 81 3 3 3 4 13 223 584 631 284 1906 495 107 1174 1081 1546 1407 1151 1809 1423 957 2218 1912 2391 2432 1976 738 2637 2081 4 808 2916 196 4 4 650

Answer 1

Set up the model.

T29 X for A H 1 K L M N 0 1 MW MW SOUTH EAST WEST Used KU 0 2 KU 3 B B 0 4 D D 0 G 0 с 0 5 G 6 C 7 F 8 K B с D E F SOUTH EAST WEST Available 488 757 1131 1319 1 838 185 1434 1417 1 104 937 541 1910 1 1981 2339 2581 1134 1 527 795 1146 1302 1 662 664 1097 1996 1 149 993 708 1897 1 1787 1301 2430 89 1 474 1174 421 2218 1 223 1081 558 1912 1 584 1546 81 2391 1 631 1407 13 2432 1 284 1151 808 1976 1 F 0 K 0 9 S S 0 10 D D 0 11 M 0 M V 12 V 0 S 0 13 S 14 W W 0 15 O 1906 1809 2916 738 1 0 0 16 M 495 2637 1 M 0 1423 957 196 650 17 L 107 2081 1 L 0 18 19 20 0 21 Distance 22

The formulas are:

T29 X fx A B с D E F G H j K 1 MW EAST WEST Available MW SOUTH E 488 1131 1 KU 2 KU 3 B SOUTH 757 185 937 2339 1319 1417 1434 1 838 104 B 4 D 1910 1 D 5 G 1981 1134 1 G 6 C 795 1 C 527 662 541 2581 1146 1097 708 2430 1302 1996 664 1 F 7 F 8 K 993 1897 1 к 149 1787 1 S 1 D 474 223 421 558 9S 10 D 11 M 12 V 13 S 14 W M 89 2218 1912 2391 2432 81 1 V 1301 1174 1081 1546 1407 1151 1809 1423 957 584 631 13 1 S 1 W 0 15 O 16 M 17 L 284 1906 495 107 808 2916 196 650 1976 738 2637 2081 1 M 1 L 18 19 20 21 Distance =SUMPRODUCT(B2:E17,02:M17) 22 22

T29 X fx D E F H J L K M - N 1 EAST WEST Available MW SOUTH EAST WEST 2 1131 1319 1 KU 3 1434 1 B 1417 1910 1 D 1134 1 G 4 541 5 2581 6 1146 7 1097 1 C 1302 1996 1897 1 F K 1 89 1 S 8 708 9 2430 10 421 11 558 Used =SUM(J2:M2) =SUM(13:M3) =SUM(14:M4) =SUM(J5:M5) =SUM(J6:M6) =SUM(J7:M7) =SUM(J8:M8) =SUM(J9:M9) =SUM(J10:M10) =SUM(J11:M11) =SUM(112:M12) =SUM(J13:M13) =SUM(J14:M14) =SUM(J15:M15) =SUM(J16:M16) =SUM(117:M17) 2218 1 D 1 M 12 81 1912 2391 2432 1 V 13 13 1 S 14 808 1976 1 w o 15 2916 738 1 2637 1 M 16 196 17 650 2081 1 L 18 19

The Solver parameters are:

X fx A B - G H J K L M N Solver Parameters XX 1 MW MW SOUTH EAST WEST Used 2 KU KU 0 Set Objective: SB$21 3 B B 0 D 0 To: O Max O Min O Value of: 0 4 D 5 G 6c G 0 C 0 By Changing Variable Cells: $J$2:$M$17 7 F F 0 1 8 K K 0 с D E F SOUTH EAST WEST Available 488 757 1131 1319 1 838 185 1434 1417 1 104 937 541 1910 1 1981 2339 2581 1134 1 527 795 1146 1302 1 662 664 1097 1996 1 149 993 708 1897 1 1787 1301 2430 89 1 474 1174 421 2218 223 1081 558 1912 1 584 1546 81 2391 631 1407 13 2432 1 284 1151 808 1976 1906 1809 2916 738 1 495 1423 196 2637 1 107 957 650 2081 1 9 S s 0 Subject to the Constraints: SN$2:$N$17 = $F$2:$F$17 10 D 1 D 0 Add 11 M M 0 12 V 1 V 0 Change 13 S S 0 Delete 14 W 1 w 0 15 O O 0 M 0 Reset All 16 M 17 L L 0 Load/Save 18 19 Make Unconstrained Variables Non-Negative 20 Select a Solving Method: Simplex LP Options 21 Distance 0 22 23 24 Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. 25 26 27 28 Help Solve Close 29

The solution is:

T29 X А B H 1 J K L M N 1 MW MW SOUTH EAST WEST Used 2 KU KU 1 0 0 0 1 3 B B 0 1 0 0 1 4 D D 1 0 0 0 1 1 1 5 G G 0 0 0 1 1 6 C с 1 0 1 7 F F 1 0 0 0 1 K 1 0 0 0 1 8 K 9 s C D E F SOUTH EAST WEST Available 488 757 1131 1319 1 838 185 1434 1417 1 104 937 541 1910 1981 2339 2581 1134 527 795 1146 1302 1 662 664 1097 1996 1 149 993 708 1897 1787 1301 2430 89 474 1174 421 2218 1 223 1081 558 1912 584 1546 81 2391 1 631 1407 13 2432 1 284 1151 808 1976 1 1906 1809 2916 738 495 1423 196 2637 1 107 957 650 2081 1 1 S 0 0 0 1 1 10 D D 0 1 0 1 11 M 1 M 1 0 0 0 1 V 0 0 1 0 1 12 V 13 S 14 W S 0 0 1 0 1 W 1 0 1 15 O 1 0 0 0 0 1 1 16 M 0 0 1 0 1 M L 17 L 1 1 0 0 0 1 18 19 20 21 Distance 22 5401

	MW	SOUTH	EAST	WEST	Available				MW	SOUTH	EAST	WEST	Used
KU	488	757	1131	1319	1			KU	1	0	0	0	1
B	838	185	1434	1417	1			B	0	1	0	0	1
D	104	937	541	1910	1			D	1	0	0	0	1
G	1981	2339	2581	1134	1			G	0	0	0	1	1
C	527	795	1146	1302	1			C	1	0	0	0	1
F	662	664	1097	1996	1			F	1	0	0	0	1
K	149	993	708	1897	1			K	1	0	0	0	1
S	1787	1301	2430	89	1			S	0	0	0	1	1
D	474	1174	421	2218	1			D	0	0	1	0	1
M	223	1081	558	1912	1			M	1	0	0	0	1
V	584	1546	81	2391	1			V	0	0	1	0	1
S	631	1407	13	2432	1			S	0	0	1	0	1
W	284	1151	808	1976	1			W	1	0	0	0	1
O	1906	1809	2916	738	1			O	0	0	0	1	1
M	495	1423	196	2637	1			M	0	0	1	0	1
L	107	957	650	2081	1			L	1	0	0	0	1
Distance	5401