Non linear fit by iteration

[nenadpmf]

Hi everyone,

I'm looking for a code that will change value of parameters A and B ( cells E2 and F2) in such a way that R approaches 1 as much as it is possible.

Thank you!

[MrShorty]

1st thought, you are trying to create a routine that has already been included in Excel. Rather than write my own routine, I prefer to use the Solver add-in for non-linear regressions in Excel. If you didn't include the Solver add-in when you installed Excel, go back to your installation source, and tell Office to install Solver.

Then you set up the spreadsheet just like you have it (with worksheet functions instead of filling in values by VBA). Column 3 is your calculated y at your best first guess for A and B. Column 4 is your squared deviations. r1c7=1-.01*sqrt(average(r2c4:r11c4)). Usually I set up my regressions to minimize the sum of the squared deviations rather than, so I'm not sure exactly how to set up the objective function on R. Maybe add a cell that is "=1-r2c7" Once you have the objective function figured out, you call Solver; tell it to "Set target cell r2c8" "to a minimum" "by changing R2c5:r2c6." If you've set up the spreadsheet correctly and given it reasonably starting values for A and B, Solver can usually converge on a good solution.