CODEWITHSUNDEEP
excelmatlabcurve-fittingsolver
user3798650
user3798650

Reputation: 13

Excel Solver Curve Fitting Failing - MatLab recast

I am having some strange problems with excel's solver. Basically what I am trying to do is curve fit my data. I have two different lines, one is my calibration line and the other is the derived line that I am attempting to match up to the calibration line. My line depends on 19 different variable parameters (Perhaps this is too many? I have tried fewer without result) and I am using solver to adjust these parameters to make the two lines as close as possible.

For Example:

The QP column contains the variables I would like changed, changing these will draw me closer or further from the calibration curve. Each subsequent value of QP must be greater than the first.

Col=B   Col=C

Power  .QP_'

1   .....    57000  
2    .....   65000  
3   .....    70000   
4   .....    80000  
5   .....    80000

Therefore my excel solver parameters look like this: C1:C19>=0,C1:C19<=100000 and C2>=C1, C3>=C2,C4>=C3... I have also tried making another column of the differences between each value and then saying that these must be diff>=0.

To compare this with my calibration curve I have taken the calibration curve data and subtracted my data derived from QP and then squared that to create my sum of the squares error. For example:

(Calibration-DerivedQP)^2=SS(x) <- where x represents the row number

Sum(SS(x))=SSE

SSE is what I have set solver to minimize. And upon changing QP everything automatically updates. There are no if statements being used and no pivot tables are used.

If I remove the parameters similar to C2>=C1 everything works perfectly, except the derived values are not feasible. But when the solver is run with these parameters, nothing gets changed and no matter which guesses I used as starting values ( so that I can ensure I haven't guessed a local minimum), the solver cannot improve upon my solution. This has led me to believe that something in my parameters is being broken, since I can very easily improve on my solution by guess and check. The rest of solvers settings are at the defaults, and the evolutionary method is used since my curve isn't smooth (I don't think) I had this working in the past and now something seems to be broken. Any ideas are appreciated! Thank you so much! Sorry if I am missing any critical information. I am also familiar with matlab and R if there are better methods in those languages.

Upvotes: 0

Views: 432

Answers (1)

user3798650
user3798650

Reputation: 13

I found the solution to my problem. I don't know if this will be helpful to anyone else since my problem vague and pretty specific to me. That being said, my problem was in the constraints. I changed some data on my excel sheet to allow for fewer constraints. An example might look like this:

Guess..........Squared......Added..................Q
-12..............(-12)^2....... 0
-16..............(-16)^2.......=(-16)^2+0.............256
+7.................(7)^2..........=(7)^2+(-16)^2+0....305

Now I allow solver to guess any number subject to minimal constraints. Essentially, what is happening now, is the excel sheet allows for any guess that solver makes to work. By squaring the numbers it give me positive values, and the added column ensures that each successive value is equal to or greater than the first. This means there are very few constraints. I also changed the solver option from evolutionary to GRG Nonlinear.

Tips for getting solver to work: Try and use the spreadsheet to set constraints (other than bounds, bounds seem to be good) wherever possible, the more constraints that I set in solver, the less likely my solution was to work.

Hope that helps, sorry if I have provided any incorrect information.

Upvotes: 1

Related Questions