Linear Programming - Fuel mix EXCEL SOLVER

[MistaMista]

Hello! I have a hard time solving this in excel using solver (required). I have the correct answer below.
I'm not sure where my constraints should be? Or how to formulate them - but my objective function is $1.05*O-type+$1.34*D-type
I have attached a screenshot of my excel below.

Screenshot of info:

Using your Excel Solver, determine how much of the O and D- type you need (for a total of 10.000 pounds) , and what the minimum cost would be.

The correct answers are:
O-Type: 3333
D-Type: 6667
Minimized cost : $12433

Screenshot of my excel+solver. Incorrect. Not feasable.

 

[KRice]

Have you tried the GRG Nonlinear engine? That seemed to work fine here:

Book1
	A	B	C	D	E
1
2		Fuel Type	H Conc (units/lb)	O Conc (units/lb)	Cost ($/lb)
3		O-Type Fuel	45	15	1.05
4		D-Type Fuel	90	6	1.34
5
6		Constraints
7		Fuel weight (lb/day)	>=10000
8		Fuel mix H (units/lb)	>=60
9		Fuel mix O (units/lb)	<=9
10
11		Basis:	1 day
12
13		Fuel weight (lb)	H units	O units	Cost
14
15	O-Type Fuel	3333.333328	149999.9998	49999.99992	3500
16	D-Type Fuel	6666.666656	599999.999	39999.99993	8933.333
17	Sums	9999.999983	749999.9988	89999.99985	12433.33
18	Conc H or O per lb		75	9
19	Constraints	10000	60	9
20	Actual relative to constraint	>=	>=	<=	minimize
Sheet2

Cell Formulas
Range	Formula
C15:E16	C15	=C3*$B15
B17:E17	B17	=SUM(B15:B16)
C18:D18	C18	=C17/$B$17

Named Ranges
Name	Refers To	Cells
solver_adj	=Sheet2!$B$15:$B$16	B17, C15:E15
solver_lhs1	=Sheet2!$B$17	C18:D18

 

[Alex Blakenburg]

I was trying to figure out how to help you without just giving you the answer.
You are close but you misread one of the criteria
On row 7 you have both fuel concentration limits using <=

• at least 60 units of hydrogen per pound of fuel mix C5>=C8
• at most 9 units of oxygen per pound of fuel mix D5<=D8

 

[MistaMista]

Have you tried the GRG Nonlinear engine? That seemed to work fine here:

Book1
	A	B	C	D	E
1
2		Fuel Type	H Conc (units/lb)	O Conc (units/lb)	Cost ($/lb)
3		O-Type Fuel	45	15	1.05
4		D-Type Fuel	90	6	1.34
5
6		Constraints
7		Fuel weight (lb/day)	>=10000
8		Fuel mix H (units/lb)	>=60
9		Fuel mix O (units/lb)	<=9
10
11		Basis:	1 day
12
13		Fuel weight (lb)	H units	O units	Cost
14
15	O-Type Fuel	3333.333328	149999.9998	49999.99992	3500
16	D-Type Fuel	6666.666656	599999.999	39999.99993	8933.333
17	Sums	9999.999983	749999.9988	89999.99985	12433.33
18	Conc H or O per lb		75	9
19	Constraints	10000	60	9
20	Actual relative to constraint	>=	>=	<=	minimize
Sheet2

Cell Formulas
Range	Formula
C15:E16	C15	=C3*$B15
B17:E17	B17	=SUM(B15:B16)
C18:D18	C18	=C17/$B$17

Named Ranges
Name	Refers To	Cells
solver_adj	=Sheet2!$B$15:$B$16	B17, C15:E15
solver_lhs1	=Sheet2!$B$17	C18:D18

View attachment 64164

Thank you! We actually have not learned to use the GRN method, I think because of the solutions can vary from the "only one solution"-tests we get, not multiple solutions, so we have to use the Simplex LP.

 

[MistaMista]

I was trying to figure out how to help you without just giving you the answer.
You are close but you misread one of the criteria
On row 7 you have both fuel concentration limits using <=

• at least 60 units of hydrogen per pound of fuel mix C5>=C8
• at most 9 units of oxygen per pound of fuel mix D5<=D8

View attachment 64170

Aahh I see now! Thank you! I changed the constraint, and got the correct result

 

[Alex Blakenburg]

You obviously had it pretty well worked out. Glad we could help.