SOLVER add-on problems

[fl4sh]

Hi, I hope this is in the right forum.
Ignore this first bit if you want. I am trying to use the solver add-on to fit a cosine function to a set of data points. The function is of the form f(x) = a cos n(x-b) + c. For each data point, I have squared the difference between it and the function. I have totalled these differences and told Solver to minimise this value by changing variables a, n, b and c. A typical least squares regression, in other words.

The problem is that solver does not to what it claims to do. I can manually input values for a, n, b and c and arrive at a lower value in the cell that solver was meant to minimise. The answer that solver gives seems to depend on the initial values of the cells that it changes. I think it might be finding some kind of local minimum, not an overall minimum.

Is it possible to set bounds for the solver function, so it evaluates all possibilities within these? Or is there some other workaround for this? As it stands at the moment, solver is all but useless because it depends entirely on the initial values I give the function.

Thanks for any help

[raypayette]

Here is a link you might find useful:
http://puccini.che.pitt.edu/~karlj/C...01/solver.html

[MrShorty]

The answer that solver gives seems to depend on the initial values of the cells that it changes. I think it might be finding some kind of local minimum, not an overall minimum.

By default, Solver uses a Newton-Raphson algorithm. This is one of the "pitfalls" of this algorithm -- if given a bad set of initial guesses, it will converge to a local minimum rather than the global minimum. What you have to do is get used to "checking" to see if Solver converged to the global minimum. If it didn't, start with a better initial guess and let it go again.

FWIW, most numerical algorithms like Solver need "reasonable" initial guesses in order to arrive at the correct solution. Just the nature of the beast.

If you know that the parameters should never fall outside of a certain range, then it would probably help to include those as constraints in the event that Solver diverges using your initial guesses. However, this might just make Solver stop at that constraint. You may still have to try again with a better intial guess.

In addition to the brief link given above, I would also suggest looking up "newton-raphson" method in a numerical methods text and on the internet so you can see basically how it works and what pitfalls it can run into so you can use it more intelligently.