Question

1) Because of population growth, the state of Washington has been given an additional seat in the House of Representatives, making a total of ten. The state legislature, which is currently controlled by the Republicans, needs to develop a plan for redistricting the state. There are 18 major cities in the state of Washington that need to be assigned to 1 of the 10 congressional districts. The next table gives the numbers of registered democrats and registered republicans in each city. Each district must contain between 150,000 and 350,000 of these registered voters. Use Evolutionary Solver to assign each city to 1 of the 10 congressional districts in order to maximize the number of districts that have more registered Republicans than registered Democrats (hint: use the SUMIF function; decision variables must be >=1 and <=10).

City Democrat Republican

1 152 62

2 81 59

3 75 83

4 34 52

5 62 87

6 38 87

7 48 69

8 74 49

9 98 62

10 66 72

11 83 75

12 86 82

13 72 83

14 28 53

15 112 98

16 45 82

17 93 68

18 72 98

USING EXCEL SHOW ALL WORK

Answer 1

This can be more simply solved as a binary linear program as follows

Formulas:

E21 =SUMPRODUCT(E3:E20,$D$3:$D$20) copy to E21:N21

O3 =SUM(E3:N3) copy to O3:O20

E23 =SUM(E21:N21)

The assignment of congressional district to each city is indicated by binary variable value equal to 1 in the decision variables matrix (E3:N20)