Gregg -

I think it is unusual that a macro would be faster than the SUMXMY2
worksheet function.

And, with Solver, you could try eight combinations of settings for
Estimates, Derivative, and Search. But I have used Solver for fitting
4-parameter functions (albeit with only a dozen data points), and I've
always used the default settings with success.

- Mike
http://www.mikemiddleton.com


"Gregg" wrote in message
...
Dana DeLouis wrote:
using least squares


Hello. My opinion is that Solver cannot do "least squares" problems very
well. When values are squared near zero, Solver cannot track "why"
values reversed direction. It just is not sophisticated enough here.
Solver uses a form of "Derivatives" to help determine its next guess.
Solver will usually abort the first time it gets confused.

With your large data set, my guess is that Solver would most likely lock
onto a wrong local minimum. Solver cannot find Global minimums.

Custom functions may also present problems. Does you function use
"Discontinuous" functions like Max, If, etc? Solver has no way of
tracking why a small input change produces large output changes via "IF"
statements. ("IF" & "Max"/"Min" probably being the two most common
errors)

I am surprised your Solver program ran for "Hours" when the target cell
had "Value" error. Usually, Solver aborts immediately.

Thank you for your reply and incite.

I should have elaborated more in my original post.
If I run solver on the problem without using a VBA macro and subroutine to
calculate least squares fit - it runs for hours...
When I replaced the cell calculations with a macro (which ran must faster
than recalculating a sheet with cells containing formulas to calculate the
SS) I received the #value error immediately.
The macro reads in the data sets from the cells into an array (variable
array in the VBA subroutine - not a cell array). - The functions used for
the fit and the associated SS errors for each point were also stored in a
variable array and the macro returned the total SS. to the target cell..
There are no discontinuous functions used and the swings in error should
be relatively small.
I use a multiplier to keep values from getting too small - this helps..

As far as global minimas - Using Excel is an approximation at best..
I should be using MathCad or the like, but I thought I might be able to
estimate the functions using a platform that is familiar to everybody who
may have to use it.
I guess I could write a simple simplex routine in VBA, but I was hoping to
avoid that.

Any suggestions would be appreciated.

Thanks,
Gregg