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
|
| Eric Desart wrote:
|
| Hello Jerry,
|
| Thanks for pointing to the link.
| However I wonder if your claim
| ....."though not as good as the chart trendline."...
| is correct.
|
| I really should be interested to see 1 single example, where I don't
| obtain the Graph trendline accuracy.
| It should allow me to test further.
| This is certainly not true for the classical ill-conditioned example
| often referred here, neither for lots of other tests I did with data
| sets from designated statistics sites.
|
| It's true that I did some work-arround in the functions.
| Reason: Excel calculates internally with more than the 15 significant
| digits, most likely using a couple more (less accurate) insignificant
| digits.
| Tests I did (results shown on my page) prove that that is the case.
|
| Anyhow I should be interested to get a dataset were my functions
| should work less accurate than the graph trendline.
| I'm always open for more tests. I couldn't find any dataset were that
| should still be the case (included the one often referred to in the NG
| here).
|
| Kind regards
| Eric
|
|
| "Jerry W. Lewis" wrote in message
...
|
| David Braden has posted VBA code to extract the coefficients directly
| from the chart into cells
|
|
http://groups.google.com/groups?selm...03%40msnews.mi
crosoft.com
|
|
| Eric Desart has published a package
|
| http://www.acoustics-noise.com/Excel...unctions.shtml
|
| that independantly compute the trendline equation, and is better
| numerically than LINEST, though not as good as the chart trendline.
|
| Jerry
|
| Lindsey Becker wrote:
|
|
| Good afternoon,
|
| I was wondering if anybody knew how to access the trendline equation in a
| chart that Excel generates. Specifically, I would like to read that
| equation into a cell.
|
| If anybody could help me with this, I would greatly appreciate it.
|
| Thank you,
| Lindsey