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