Jerry thanks once more.
It will take probably some time before I can study the Add-In in
detail again (as said this thing was really time-consuming)
What I easily can do for now is removing those options, meaning that
the add-in will automatically return the best result.
I integrated those options in fact for myself, and people interested
to see the effect of the different approaches.
What it then automatically does is trying to use or return a formula
without this additional scaling, and only when the results can be
improved by this scaling (basically making the data set less
ill-conditioned)it will do that internally.
I left control to the user now, but I can make the Auto option an
internal standard.
Complete reprogramming this thing will call for much more time.
Kind regards
Eric
"Jerry W. Lewis" wrote in message ...
You are welcome.
In rereading my response, the last sentence of the 2nd paragraph may be
unclear. When I said "It is worth noting that you get 2-3 extra correct
figures" (for all but the intercept with the Auto option and algebraic
expansion), that is 2-3 correct figures more than Excel, i.e. 11-12
correct figures.
Excel gets a lot of justifiable abuse for poor algorithms in probability
and statistics functions, but the chart trendline is one thing in this
arena that they did extremely well. It is numerically better than
S-PLUS and R; SAS doesn't even come close. It is unfortunate and
inexplicable why they built in such a robust algorithm and didn't make
it available as a worksheet function.
Origin 7 is the only package I have seen that can fit this problem with
significantly better accuracy (13 figures per coefficient, though it is
not obvious how to get it to display them), without user specified
precision. I suspect that they accomplish this by supplementing a very
good algorithm with the 10-byte floating point extended precision that
is internal to the Pentium math coprocessor.
Jerry
Eric Desart wrote:
Thanks Jerry,
I'm going to play a bit with this information.
I come back on that. Will take some time. This was really a time consuming
Add-In.
Best Regards
Eric
"Jerry W. Lewis" schreef in bericht
...
| My statement has reference to to the accuracy of coefficient estimates,
| and is demonstrated by the example on your "TRENDplus-TRENDEST(1)"
| worksheet. X'X is very ill-conditioned (condition number ~10^30), which
| means that it is much easier get accuracy on predicted values (for
| observed x-values) than to get accuracy for estimates (which translates
| to accuracy for interpolation/extrapolation)
|
| When I fit the data with the Auto option, and expand the scaled equation
| algebraically (to avoid further rounding issues), the LRE (roughly the
| number of correct figures) is 3.7 vs. ~9 for the Excel trendline. It is
| worth noting that you get 2-3 extra correct figures on all the other
| coefficients, however.
|
| When I fit the data with the Enhanced option (to sidestep the
| unrealistic algebraic expansion) the LRE for each coefficient is 1.8 vs.
| ~9 for the Excel trendline.
|
| LRE stands for "log relative error" and is calculated as
|
| LRE = -LOG10( ABS(est-exact)/exact) )
|
| I doubt that Excel uses more than 15 figures internally. The issue is
| how well you use the precision available to you. For instance VARP(x)
| would require twice the precision to achieve the same accuracy as
| DEVSQ(x)/COUNT(x) for numerically challenging problems.
|
| The algorithms at
| http://lib.stat.cmu.edu/apstat/274
| http://lib.stat.cmu.edu/apstat/75
| give nearly the same accuracy as the Excel trendline, without extended
| precision. With extended precision they would beat it hands down.
|
| Jerry
|