Re: Solver - macro combination

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 *** 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


.