Helped Needed: Linear/nonlinear Optimization with Solver

[TomBurton_021978]

Hi guys. I am currently working on an simple optimization problem that has a two non-linear constraint. Nevertheless, I want to solve this problem (I am required to do so) using Simplex LP and I struggle how to convert these constraints into linear constraints or how to approach this problem in general.

To make it clearer, a mathematical formulation of this problem would be:

Maximize R=550x+1100y

where quantities x and y have to be chosen in order to maximize R.

There are 4 linear constraints (I know how to handle them) and 2 nonlinear constraints, which both include a ratio of x and y:

(5,379+x)/(3,260+y) ≥ 1.4
(5,379+x)/(3,260+y) ≤ 1.75

Would really appreciate your help on how to convert these 2 constraints in some way or on how to approach this problem in general

Best Tom

 

[i_nth]

Rearrange the constraints to remove the division, e.g.
(5,379+x)/(3,260+y) ≥ 1.4
becomes
(5,379+x) ≥ 1.4*(3,260+y)
This form is linear.

 

[Macropod]

Cross-posted at:
https://chandoo.org/forum/threads/help-needed-linear-nonlinear-optimization-problem-solver.41354/
https://www.excelforum.com/excel-ge...inear-nonlinear-optimization-with-solver.html
https://www.excelguru.ca/forums/sho...ded-Linear-nonlinear-Optimization-with-Solver
http://www.msofficeforums.com/excel/42089-helped-needed-linear-nonlinear-optimization-solver.html
http://www.vbaexpress.com/forum/sho...ded-Linear-nonlinear-Optimization-with-Solver
(and who knows where else)
Kindly read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html Compliance is NOT optional.

 

[joeu2004]

(5,379+x) ≥ 1.4*(3,260+y)
This form is linear.

Yes, but not in the form that is normally required for Simplex LP. In fact, I'm surprised that form works at all.

I think we should take the algebra "one step" further. But admittedly, when I do, I get the same max, using the Excel file that Tom provides in excelforum.com.

Referencing that Excel file, the original formulas are:

F54 >= H54
F54: =($D$6+$C$39)/($D$5+$D$39)
H54: =C16

F55 <= H55
F55: =($D$6+$C$39)/($D$5+$D$39)
H55: =D16

I believe the correct Simplex LP formulas should be:

F54: =C39 - C16*D39
H54: =C16*D5 - D6

F55: =C39 - D16*D39
H55: =D16*D5 - D6

And the Solver contraints are:
F54 >= H54
F55 <= H55
F44 <= H44
F45 <= H45
F49 <= H49
F50 <= H50

 

[i_nth]

Yes, but not in the form that is normally required for Simplex LP.

The way you've expressed the formulation is "standard form", i.e. all variables on the left-hand side and all constants on the right-hand side.
While this is a good thing to do, for presentation purposes, it isn't necessary for Solver to work with the formulation as the sets of equations are mathematically equivalent.