Excel solver not Producing Solution for Grade Proportion and Bussing Cost Optimization

[madhushrisridhar]

I am trying to solve a bussing cost minimization problem using Excel Solver. The goal is to assign students from various areas to three schools while respecting:

Capacity Constraints:

School 1: 900 studentsSchool 2: 1100 studentsSchool 3: 1000 students

Grade Proportion Constraints:
Each school's 6th, 7th, and 8th grades must constitute between 30% and 36% of the total student population at that school.

Objective:
Minimize the total bussing cost, calculated as the sum of the number of students assigned to each school multiplied by the cost per student.

What I Have Done:
Objective Function: Set up using SUMPRODUCT to calculate total bussing costs.
Constraints:
School capacity: Total students assigned to each school do not exceed the specified limits.
Grade proportions: Each grade's student count is constrained between 30% and 36% of the total students at that school.
Solver Setup:
Objective Cell: Minimize total bussing costs.
Variable Cells: Students assigned to schools
Solving Method: Simplex LP.
Non-negativity: Ensured all variable cells are ≥ 0.
The Challenge:
Despite there being a feasible solution (confirmed manually), Solver does not produce the correct result. I suspect the issue might be with how the grade proportion constraints are being interpreted or applied.

What I Need:

Guidance on correctly setting up the grade proportion constraints in Solver.

Verification of whether the objective function or constraints have any formulation errors.

Any tips on debugging Solver when the solution exists but isn’t being found.

Optimisation problem.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2
3		Data	Area	% in 6th grade	% in 7th grade	% in 8th grade	School 1	School 2	School 3
4			1	0.32	0.38	0.3	300	0	700
5			2	0.37	0.28	0.35	-	400	500
6			3	0.3	0.32	0.38	600	300	200
7			4	0.28	0.4	0.32	200	500	-
8			5	0.39	0.34	0.27	0	-	400
9			6	0.34	0.28	0.38	500	300	0
10
11		Solution:
12				No of students assigned
13		Best allocation	Area	School 1	School 2	School 3	Total		Number of students
14			1	0	0	0	0	=	450
15			2	0	0	0	0	=	600
16			3	0	0	0	0	=	550
17			4	0	0	0	0	=	350
18			5	0	0	0	0	=	500
19			6	0	0	0	0	=	450
20			Total in school	0	0	0
21				≤	≤	≤
22			Capacity	900	1100	1000
23
24		Grade Constraints
25				School 1	School 2	School 3
26			0.3	0	0	0
27				≤	≤	≤
28		Number of people	6th grade	0	0	0
29			7th grade	0	0	0
30			8th grade	0	0	0
31				≤	≤	≤
32			0.36	0	0	0
33
34				School 1	School 2	School 3
35		% of people	6th grade	#DIV/0!	#DIV/0!	#DIV/0!
36			7th grade	#DIV/0!	#DIV/0!	#DIV/0!		Total Busing Cost		£ -
37			8th grade	#DIV/0!	#DIV/0!	#DIV/0!
38
39
Sheet1

Cell Formulas
Range	Formula
G14:G19	G14	=D14+E14+F14
D20:F20	D20	=SUM(D14:D19)
D21:F21,D27:F27,D31:F31	D21	=UNICHAR(8804)
D26:F26	D26	=$C$26*D20
D28	D28	=SUMPRODUCT(D4:D9,D14:D19)
E28	E28	=SUMPRODUCT(D4:D9,E14:E19)
F28	F28	=SUMPRODUCT(D4:D9,F14:F19)
D29	D29	=SUMPRODUCT(E4:E9,D14:D19)
E29	E29	=SUMPRODUCT(E4:E9,E14:E19)
F29	F29	=SUMPRODUCT(E4:E9,F14:F19)
D30	D30	=SUMPRODUCT(F4:F9,D14:D19)
E30	E30	=SUMPRODUCT(F4:F9,E14:E19)
F30	F30	=SUMPRODUCT(F4:F9,F14:F19)
D32:F32	D32	=$C$32*D20
D35:F35	D35	=D28/D20
D36:F36	D36	=D29/D20
D37:F37	D37	=D30/D20
J36	J36	=SUMPRODUCT(D14:F19,G4:I9)

Named Ranges
Name	Refers To	Cells
solver_adj	=Sheet1!$D$14:$F$19	J36, D20, D28:D30, G14
solver_lhs1	=Sheet1!$D$20:$F$20	D32, D26, D35:D37
solver_lhs10	=Sheet1!$F$17	J36, F28:F30, F20, G17
solver_lhs5	=Sheet1!$D$28:$F$28	D35
solver_lhs6	=Sheet1!$D$29:$F$29	D36
solver_lhs7	=Sheet1!$D$30:$F$30	D37
solver_rhs2	=Sheet1!$D$28:$F$28	D35
solver_rhs3	=Sheet1!$D$29:$F$29	D36
solver_rhs4	=Sheet1!$D$30:$F$30	D37