[Lindsey Becker]

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

[Jerry W. Lewis]

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

[Eric Desart]

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

[Jerry W. Lewis]

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

[Eric Desart[_2_]]

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
|