×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Linear Programming in Excel - any advise?
2

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?

JMW
www.ViscoAnalyser.com

 

RE: Linear Programming in Excel - any advise?

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

RE: Linear Programming in Excel - any advise?

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.

RE: Linear Programming in Excel - any advise?

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: Eng-Tips.com Forum Policies  http://eng-tips.com/market.cfm?

RE: Linear Programming in Excel - any advise?

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)'  ?

RE: Linear Programming in Excel - any advise?

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)'  ?

RE: Linear Programming in Excel - any advise?

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)'  ?

RE: Linear Programming in Excel - any advise?

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)'  ?

RE: Linear Programming in Excel - any advise?

(OP)
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
www.ViscoAnalyser.com

 

RE: Linear Programming in Excel - any advise?

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

RE: Linear Programming in Excel - any advise?

(OP)
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
www.ViscoAnalyser.com

 

RE: Linear Programming in Excel - any advise?

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 [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?

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

JMW
www.ViscoAnalyser.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources