Linear Programming in Excel - any advise? 2

[jmw]

Has anyone here used the Excel Solver for linear programming?
If so how easy did they find it and does it then easily translate to a web program?
Any pitfalls?

There seems quite a lot on the web and one source seems a good starting point.
Any recommended aids?

JMW

http://www.ViscoAnalyser.com

 

[MiketheEngineer]

You might want to check out Tek-Tips a sister site to this and where the computer geeks live

 

[xwb]

You probably have to use javascript for this. If you do, use a browser with compiled javascript like Chrome otherwise you'll be there forever.

If you are writing it in Javascript and you are working in windows, you can try it first as a console program before putting in the web bits.

 

[GregLocock]

If by linear programming you mean identifying which regions of a cartesian plane satisfy a set of equations

Y>Mn*X+Cn

Y<Mo*X+Cp

Y=Mp*X+Cp

for many n, o, p

then solver is a bad choice.

If instead you can write a cost function that smoothly rewards progress towards some target region then solver will work nicely.

Things you might think about - what if there is more than one region that works?

can you make a good initial guess?

The wiki article is turgid but useful.



Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[electricpete]

I think solver can do the standard linear programming problem pretty easily.

Google ...
linear programming in excel
... gives many results



The thing that might seem tough is to solve a constraint involving linear combination of multiple independent variables. It is easy if you generate new independent variables that represents the linear combo, then apply the constraint to the new variable.

For example:
Maximize Y = A1 X1 + A2 X2
subject to
B1 X1 + B2 X2 < 5

Define new cell as with formula B1 X1 + B2 X2 and constrain that cell to be less than 5. The constraint is now captured.



=====================================
(2B)+(2B)' ?

 

[electricpete]

Whoops, may not be that easy. I'm not sure it makes sense to apply a constraint to an equation that has a forumla (since solver will overwrite the formulas). At any rate, there are many google links on the subject, it is certainly doable.

=====================================
(2B)+(2B)' ?

 

[electricpete]

I just tried it out, and the constraints can indeed be applied to independent variable = results of a formula .... not just to the independent variables (the ones that are varied by solver). So my first answer seems right, my correction was unnecessary. Sorry.

=====================================
(2B)+(2B)' ?

 

[electricpete]

Correction in bold below. Should've been:

I just tried it out, and the constraints can indeed be applied to dependent variable = results of a formula .... not just to the independent variables (the ones that are varied by solver). So my first answer seems right, my correction was unnecessary. Sorry.

I promise, I'm done now.

=====================================
(2B)+(2B)' ?

 

[jmw]

Thanks for the help everyone.
Like most of my spreadsheets, it may not be elegant, it just has to work.

This is a 3 or 4 stream blend where I am looking for the lowest cost dependent on which property is selected for the target.
I've done a two stream to sort out all the other bits and pieces, I now need to expand it and add in the linear programming to find the lowest cost solution.
I can't exclude the possibility that there may be multiple solutions but I guess I'll need to address that when the time comes and decide if it matters. If it does matter than presumably I'll need another constraint.

JMW

http://www.ViscoAnalyser.com

 

[poli60]

JMW,
Excel Solver is appropriate and easy to use for problems like yours.
I've used it extensively in the past for blending of refinery streams and searching of economic optimization.
Solver showed me some wrong results with the increased complexity of the models (ie hundreds of optimization parameters).
Linear programming and Excel spreadsheets are two of my preferred "applications".
Regards

 

[jmw]

poli60
thanks.

Incidentally, I'd rather like to be able to do this without solver if possible. This has to be converted into a web application at some point.


JMW

http://www.ViscoAnalyser.com

 

[poli60]

JMW,
so you want to apply the "fundamentals" of linear programming inside the excel spreadsheet. It's an interesting challenge: my professors would be very proud of you
If I'd have to approach this problem, I'd think about something in VBA helping to solve the iterative equations... unfortunately 30 years of work (rust?) have been diluted a lot the knowledges acquired during the old good days of the university.
I'll be glad whether you'd like to share your basis, approach and progresses. For instance:
- which "streams" are you going to blend?
- which parameters (other than viscosity ) will you consider?
- which set of blending rules?
- ......
Kind regards

 

[jmw]

Poli60, er, if possible I'd like an off site discussion.
Please look at my website.

JMW

http://www.ViscoAnalyser.com

 

[poli60]

The printout of an application of Excel Solver to (very simplified) gasoline blending is attached.
Just to show the methodology....