Best Problems Solved Using Excel Solver
Best Problems Solved Using Excel Solver
(OP)
Any link to a web page will be highly appreciated.
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS Come Join Us!Are you an
Engineering professional? Join Eng-Tips Forums!
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting GuidelinesJobs |
Best Problems Solved Using Excel Solver
|
Best Problems Solved Using Excel SolverBest Problems Solved Using Excel Solver(OP)
Any link to a web page will be highly appreciated.
Red Flag SubmittedThank you for helping keep Eng-Tips Forums free from inappropriate posts. Reply To This ThreadPosting in the Eng-Tips forums is a member-only feature.Click Here to join Eng-Tips and talk with other members! |
ResourcesThe world has changed considerably since the 1980s, when CAD first started displacing drafting tables. Download Now
Prototyping has always been a critical part of product development. Download Now
As the cloud is increasingly adopted for product development, questions remain as to just how cloud software tools compare to on-premise solutions. Download Now
Engineering-centric businesses face a number of challenges today, but unmanageable design and change processes don’t need to be counted among them. Download Now
|
RE: Best Problems Solved Using Excel Solver
I have used Solver in quite a few spreadsheets over the years, becoming a bit of a Solver addict in the process. Part of Solver's appeal to me is that it can be applied to solving non-linear equation systems, as well as being applied to more obvious maxima/minima problems.
I have recently placed three of my spreadsheets on my web page, for public availability. Two of these are straight-forward maximization problems, using Solver to position a load on a slab in such a way as to maximize the resulting bending moment. The third one uses it to solve a set of highly non-linear simultaneous equations for the tensions etc in an extensible cable with a point load somewhere along its length.
The web site is at http://rmniall.com
One thing I have found over the years is that, when developing spreadsheets for use by people whose skill levels you cannot anticipate, you must minimize the mistakes your user can make INADVERTENTLY. For this reason, all three of the spreadsheets have their execution of Solver outside the direct control of the user. (The corollary to this is that if your user really wants to mess things up, there is no way you can prevent it.)
RE: Best Problems Solved Using Excel Solver
Here's a link to a page about using Solver for non-linear curve fitting. Not a "real application", but people might find it useful anyway.
http://newtonexcelbach.wordpress.com/2011/03/01/no...
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Best Problems Solved Using Excel Solver
The model parameters would be things like R1, R2, L1, L2, Xm, Rm... call them x1, x2, x3 etc for simplicity.
The operating characteristics would be things like Efficiency at full load, power factor at full load, efficiency at half load, no load amps, starting current, starting power factor, breakdown torque, starting current etc.... call them X1, X2, X3, etc.
So we have
X1 = f1(x1, x2, x3...)
X2 = f2(x1, x2, x3...)
X3 = f3(x1, x2, x3...)
etc.
Where
we know all the functions f1, f2, f3
we know selected operating characteristics X1, X2, X3
we want to know the model parameters x1 x2 x3 (so that we can calculate other of the operating parameters which were not initially known).
So the basic stumbling block is that the model is easily solved for operating characteristics from model parameters. We want to reverse it and solve the model parameters from the operating characteristics. No closed for method exists.
So we need to put it into excel. To put these relationships f1, f2, f3 into excel, we need some guess at the values of x1, x2, x3... call our guess x1’, x2’, x3’
When we plug our guesses x1’, x2’, x3’ into the model, we get estimates of operating characteristics X1’, X2’, X3’ where
X1’ = f1(x1’, x2’, x3’)
X2’ = f2(x1’, x2’, x3’)
etc
The goal is to find the value of guesses x1’, x2’, x3’ which will make calculated X1’, X2’ X3’ match the known X1, X2, X3. But solver doesn’t work with multiple equations, it wants to minimize one result. So we need to combine these equations... logical starting point is sume of squares of residual errors.
e1 = X1 – X1’ = X1 – f1(x1’, x2’, x3’)
e2 = X2 – X2’ = X2 – f2(x1’, x2’, x3’)
etc
SSE = e1^2 + e2^2 + e3^3
So you can see we have excel spreadsheet which starts with guesses x1’, x2’, x3’, uses them to compute X1’, X2’, X3’, compare those to targets (X1, X2, X3) to come up with errors (e1, e2, e3)..... which are roled into a total SSE
The user of solver should now be obvious: set solver up to vary x1’, x2’, x3 in order to minimize SSE. If we find SSE suitably low, we may have a good solution.
Actually there is quite a bit more you should do to test the goodness of your solution and look at how the solution behaves with varying initial conditions. One very useful technique for varying initial conditions is to use a weighted sum of errors instead of a simple sum of errors. Run the optimization and use the solution as the guess for the next optimization, but with new weighting factors. Often if you set the highest weighting factor to the error that is the highest, it leads you toward a good solution.
=====================================
(2B)+(2B)' ?
RE: Best Problems Solved Using Excel Solver
=====================================
(2B)+(2B)' ?
RE: Best Problems Solved Using Excel Solver
RE: Best Problems Solved Using Excel Solver
RE: Best Problems Solved Using Excel Solver