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