Using Excel's SOLVER add-on

  • Thread starter flash
  • Start date
  • #1
68
0
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 :smile:
 

Answers and Replies

  • #2
hotvette
Homework Helper
996
5
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".
 

Related Threads on Using Excel's SOLVER add-on

  • Last Post
Replies
4
Views
729
  • Last Post
Replies
2
Views
2K
  • Last Post
Replies
1
Views
1K
  • Last Post
Replies
1
Views
16K
  • Last Post
Replies
4
Views
2K
Replies
2
Views
3K
  • Last Post
Replies
3
Views
2K
  • Last Post
Replies
1
Views
1K
Replies
7
Views
6K
Top