Best Problems Solved Using Excel Solver 5

[McDermott1711]

Any link to a web page will be highly appreciated.

 

[Denial]

It's not immediately obvious what you are looking for, or why you are looking for it.

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 [tt]

http://rmniall.com

[/tt]

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.)

 

[IDS]

Denial - my guess is he was looking for just the sort of thing that you linked to.

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/non-linear-regression-1/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

An induction motor equivalent circuit model (including model parameters) can be used to predict a wide variety of operating characteristics of the motor.

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

 

[electricpete]

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.

Sorry, my terminology wandered here. By "initial conditions", I meant the guesses x1', x2', x3'


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

 

[cloa]

He means what sort problems and initial values does Solver do poorly with i.e. it doesn't converg.

 

[McDermott1711]

Sorry, I was away from this forum for a while. Thank you all for your response. Actually, there wasn't any specific purpose for that request. I just needed some exercise for my brain muscles (burning phosphorous).

 

[Denial]

For whose brain muscles? -))