Solver - macro combination

[Gregg]

I think I maybe barking up the the wrong tree...
I have problem that takes hours to optimize using solver.
(5 large data sets fit to 4 equations - using least squares.
I wrote a macro to determine the SS of the fit error ( in hopes of
speeding things up a bit.)
My target cell is a function that calls the error fitting subroutine.and
returns the SS error
I pass all the parameters (cells) I want to change through the macro so
it recalculates automatically

The macro works fine, the subroutine works.
When I use solver I get value# in the target cell.
I'm using Excel 2000 - is there a work around or am I trying to do
something that is outside of solvers capability?

Thanks in advance,
Gregg

 

[Dana DeLouis]

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.

 

[Gregg]

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

 

[Gregg]

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

 

[Gregg]

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

 

[Gregg]

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

 

[Gregg]

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

 

[Mike Middleton]

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

 

[Mike Middleton]

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

 

[Gregg]

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

I think I'm pushing Excel a little too hard
I have 6000+ data points fitted to am equation with 9 adjustable
parameters (exponents etc ..).
It takes Excel ~ 10 sec to recalculate the cells and about 6 sec to run
the macro.
I like to use solver interactively - adjusting the starting points
calculation method etc...
I can take some pretty good educated guesses about the starting
parameters, On similar problems it take at least 3-6 runs before I get a
reasonable estimate. - My main problem with this problem is the time it
takes to run.

Of course I could always ask for a faster computer ;-)
Gregg