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" <post_a_reply@no_e-mail.com> 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
| >
| >
| >
| >>David Braden has posted VBA code to extract the coefficients directly
| >>from the chart into cells
| >>
|crosoft.com
| >>
| >>
| >>Eric Desart has published a package
| >>
| >>
http://www.acoustics-noise.com/Excel/Add-In-Functions.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
| >>>
|
