How can I improve the accuracy of Excel's SOLVER add-on for curve fitting?

[flash]

Hi
I'm trying to use Excel to find a cosine function, f(x)=a cos n (x-b) + c, that best fits a whole lot of data points. I'm doing this using the Solver add-on in Excel. I have it set up to minimise the sum of the squared differences between each data point and the function's value. I'm pretty confident that its all set up correctly.
The problem is, solver does NOT do what it claims to do. It says it minimises the value in the cell by changing variables a, n, b and c but I can manually input variables that give a much lower value. Solver gives a different value depending on what is initially in the cells of variables a, n, b and c. I believe it is finding some kind of local minimum which is not the overall minimum.
Any ideas? I was hoping someone might have come across this problem with solver and know a workaround

 

[hotvette]

I know the question was several months ago, but this reply might have some use, even if for future activity. I haven't been very impressed with the Excel Solver, however I am very happy with the optimization Excel Add-in you can get from here:

http://digilander.libero.it/foxes/SoftwareDownload.htm

scroll down the page until you find "Didactic Optimization Tool for EXCEL".

Having said that, I'm not sure any tool can do a very good job on finding a global minimum. I think they all will provide a different answer depending on the starting point. Global optimization is very difficult. As a professor friend of mine told me: "finding the global optimum requires trying all possibilities".

 

[Architeuthis Dux]

I understand your frustration with the limitations of Excel's SOLVER add-on. It is important to note that while SOLVER can be a useful tool for optimization problems, it is not a perfect solution and may not always give the desired results. In this case, it seems like SOLVER may be finding a local minimum instead of the overall minimum, which is a common issue with optimization algorithms.
One workaround could be to try different starting values for your variables a, n, b, and c to see if that leads to a better solution. You could also try using a different optimization algorithm or a different software specifically designed for curve fitting, such as MATLAB or Python libraries like SciPy or NumPy.
It is also important to carefully check your data and the setup of your optimization problem to ensure that everything is correct and there are no errors or inconsistencies that could be affecting the results.
Overall, while Excel's SOLVER add-on can be a useful tool, it is important to be aware of its limitations and to explore alternative methods when necessary.