Linear Programming in Excel - any advise?
Linear Programming in Excel - any advise?
(OP)
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?
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?





RE: Linear Programming in Excel - any advise?
RE: Linear Programming in Excel - any advise?
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.
RE: Linear Programming in Excel - any advise?
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: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Linear Programming in Excel - any advise?
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)' ?
RE: Linear Programming in Excel - any advise?
=====================================
(2B)+(2B)' ?
RE: Linear Programming in Excel - any advise?
=====================================
(2B)+(2B)' ?
RE: Linear Programming in Excel - any advise?
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)' ?
RE: Linear Programming in Excel - any advise?
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
www.ViscoAnalyser.com
RE: Linear Programming in Excel - any advise?
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
RE: Linear Programming in Excel - any advise?
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
www.ViscoAnalyser.com
RE: Linear Programming in Excel - any advise?
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 [thumbsup2]
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 [bigsmile] ) will you consider?
- which set of blending rules?
- ......
Kind regards
RE: Linear Programming in Excel - any advise?
Please look at my website.
JMW
www.ViscoAnalyser.com
RE: Linear Programming in Excel - any advise?
Just to show the methodology....