Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Using Excel's SOLVER add-on

  1. Feb 26, 2007 #1
    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:
  2. jcsd
  3. Aug 1, 2007 #2


    User Avatar
    Homework Helper

    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:


    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".
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook