Re: Solver - macro combination

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I forgot to mention that, although "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," I usually have to try a
variety of starting points for the parameters before I eventually get an
appropriate Solver result.

- Mike

"Mike Middleton" <mike@xxxxxxxxxxxxxxxxx> wrote in message
news:%23hRto6JXHHA.600@xxxxxxxxxxxxxxxxxxxxxxx
Gregg -

I think it is unusual that a macro would be faster than the SUMXMY2
work*** 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" <nospam123gcrume@xxxxxxxxxxxxxxx> wrote in message
news:45E7519E.6060101@xxxxxxxxxxxxxxxxxx
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






.

Quantcast