Excel solver

Question

Hi all!

I am having some trouble with using solver to help me analyse my model. For some context, I have some data for which there is a linear and curved section, hence it is looking like a non-smooth function. I am trying to find the x-axis data point for which this transition in the graph happens. Hence, I have set up the excel with an 'IF' function, where it fits the linear section according to y=ax+b below the transition time 't(t)', and the curved section according to a nonlinear regression equation above time t(t). It is solving for the transition time which results in the lowest cumulative error between the data and the calculated values.

However, using the evolutionary solver I am obtaining a different value for t(t) every time I run it. I have browsed frontline systems guide pages online, but am still fairly stuck on the best approach to tackle this challenge.

Answer 1

Here is a macro version - I split the population of points in a progressive manner, and recalculate the error each time after running solver.

Solver Macro File

Answer 2

Thanks for the info Bernie. Sounds good in theory, however with practically no knowledge on VBA/macros, I am unsure of how to set this system up. Any advise or guidance you could offer or point me in the direction of?

Answer 3

Being a VBA guy, I would solve this be setting the initial break point at my best guess, then stepping by one point (in both directions) until the cumulative error minimum is found, and successesive steps lead to larger and larger errors. I would also investigate using three or four or five different xones for fitting the curves (depending on the size of the data set) since there may be a 'transition' effect that really isn't fit well be either models.

Answer 4

For a dispositive or more constructive answer, you should provide a concrete example.

Ideally, upload a simple Excel file to a file-sharing website, and post the download URL. I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum. But IMHO, if you use onedrive, be sure the download URL does not allow others to edit the file. That avoids accidental (as well as purposeful) changes. (I'm all thumbs. sigh)

IMHO, the Evolutionary method is a poor choice. Since it generates random solutions until some terminating condition, I can imagine that it might derive different results each time.

But ultimately, it depends on the Solver set-up, notably the constraints and objective (min, max, value). (Presumably you specify "min", since you say the goal is "the lowest cumulative error").

At the very least, you should provide that information, as well as sample data.

Bear in mind that Solver is sensitive to the types of formulas that we might use. Solver does not look at all the formulas that it might calculate. But certain formulas might cause "discontinuities" that can cause Solver to terminate the iterations prematurely, with or without error. For example, IF formulas (I have set up the excel with an 'IF' function).

Also, as the Frontline website expains (click here):

When the Evolutionary Solver is being used, this message means that the “fitness” of members of the current population of candidate solutions is changing very slowly.  More precisely, the Evolutionary Solver stops if 99% or more of the members of the population have “fitness” values whose relative (i.e. percentage) difference is less than the Convergence tolerance on the Task Pane Engine tab.  The “fitness” values incorporate both the objective function and a penalty for infeasibility, but since the Solver has found some feasible solutions, this test is heavily weighted towards the objective function values.  If you believe that the Solver is stopping prematurely when this test is satisfied, you can make the Convergence tolerance smaller, but you may also want to increase the Mutation Rate and/or the Population Size, in order to increase the diversity of the population of trial solutions.  For more information, see the discussion of “Evolutionary Solver Stopping Conditions” below.

Explore the Frontline website for more tips. There is a lot of detail there.

PS.... Some more quotes from the Frontline website.

Click here:

Microsoft Excel provides a very rich formula language, including many built-in functions that are discontinuous or non-smooth.  These functions cannot be used with the Simplex LP Solving method.  Discontinuous functions cause considerable difficulty, and non-smooth functions cause some difficulty for the GRG Nonlinear Solving method.  The Evolutionary Solving method can handle these functions, but you’ll “pay a price” in solution time and quality.  Some models can only be expressed with the aid of these functions; in other cases, you have a degree of choice in how you model the real-world problem, and which functions you use. By far the most common discontinuous function in Excel is the IF function where the conditional test depends on the decision variables, as in the example =IF(C1>10,D1,2\*D1) where C1 is a variable.

And click here:

If your objective and constraints are *smooth nonlinear* functions of the decision variables, solution times will be longer.  If the problem is *convex*, you can be confident of finding a *globally optimal* solution, but if it is *non-convex*, you can only expect a *locally optimal* solution – and even this may be hard to find.  The GRG Nonlinear Solving method is designed for these problems.

If your objective and constraints are *non-smooth* and *non-convex* functions of the decision variables (for example if you use IF, CHOOSE and LOOKUP functions whose arguments depend on decision variables), the best you can hope for is a “good” solution (better than the initial values of the variables), not a locally or globally optimal solution.  The Evolutionary Solving method is designed for these problems.