Helped Needed: Linear/nonlinear Optimization with Solver

TomBurton_021978

New Member
Joined
Mar 29, 2019
Messages
1
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

i_nth

New Member
Joined
Mar 28, 2019
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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.
 
  • Like
Reactions: shg

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,983
Office Version
  1. 2010
Platform
  1. Windows
(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
 
Last edited:

i_nth

New Member
Joined
Mar 28, 2019
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,535
Messages
5,602,217
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top