Data Table for Trendline

[Ken Wright]

Does anyone know of a way to get excel to show the data table for a
trendline. I am using a Polynomial to the 6th Order on 5 years worth of
monthly data points. I can see the line but i would like to be able to get
to a table of values and then use them in calculations.

Regards
Ken............................

[Jerry W. Lewis]

If you click on the data series, it the formula window will show the
cell references where the data are stored.

If you want fitted values for the polynomial, instead of the data
itself, use the TREND() function.

If you want the coefficients of the polynomial, use the LINEST() function.

Help for TREND() and LINEST() are extremely misleading. Neither is
restricted to straight lines. They fit "linear models", which means
models that are linear in the unknown coefficients (including polynomials).

For a 6th order polynomial with x's in A1:A20 and y's in B1:B20, use the
array formula =LINEST(B1:B20,A1:A20^{1,2,3,4,5,6})

Jerry

[Martin Brown]

"Jerry W. Lewis" wrote:

> If you click on the data series, it the formula window will show the
> cell references where the data are stored.
>
> If you want fitted values for the polynomial, instead of the data
> itself, use the TREND() function.
>
> If you want the coefficients of the polynomial, use the LINEST() function.
>
> Help for TREND() and LINEST() are extremely misleading. Neither is
> restricted to straight lines. They fit "linear models", which means
> models that are linear in the unknown coefficients (including polynomials).
>
> For a 6th order polynomial with x's in A1:A20 and y's in B1:B20, use the
> array formula =LINEST(B1:B20,A1:A20^{1,2,3,4,5,6})
>

Unfortunately, LINEST is so badly implemented that for anything remotely
difficult and a polynomial fit beyond cubic it will not get the same answer for
the polynomial as the Trendline in the chart.

If you set the coefficients of the chart trendline polynomial to show maximum
significant digits and parse the formula you can get at the better set of
coefficients (they are useless if truncated).

Fitting a 6th order polynomial usually implies that you don't understand your
data....

Regards,
Martin Brown

[Jerry W. Lewis]

Martin,

Please give an example data set that shows the Trendline fit to be
better than LINEST.

Forming the Normal Equations (as in LINEST, SLOPE, VAR, STDEV, etc.) is
a mathematically elegant, but numerically poor way to compute Least
Squares estimates. I am well aware that LINEST will do poorly with
numerically challenging data, but this is the first I have heard that
Trendline on the graph may do better.

Jerry

Martin Brown wrote:

>
...

[Thomas Bartkus]

"Martin Brown" <martin...@pandora.be> wrote in message
news:3D7EF6BA...@pandora.be...
<Snip>

> Fitting a 6th order polynomial usually implies that you don't understand
your
> data....
>
> Regards,
> Martin Brown

Amen!

-
Thomas Bartkus
www.BartkusConsulting.com

[Bernard Liengme]

I'd like to see some example to support the contention that LINEST is so
poorly implement that ....
Have you try to fit data from the NIST databases and got poor results?
Best wishes
Bernard

"Martin Brown" <martin...@pandora.be> wrote in message
news:3D7EF6BA...@pandora.be...
>
>

[Martin Brown]

"Jerry W. Lewis" wrote:

> Martin,
>
> Please give an example data set that shows the Trendline fit to be
> better than LINEST.

Try the start of the thread for the raw data titled "Polynomial Trendline Bug in
Excel 2000?".

http://groups.google.com/groups?hl=nl&lr=&ie=UTF-8&selm=393E5EAC.AD4A46ED%40pandora.be

When I tested the goodness of fit of the two methods on the problematic data that
someone had complained about to my surprise the "Bug" was that trendline got a much
better least squares polynomial fit solution than LINEST on the same dataset.

> Forming the Normal Equations (as in LINEST, SLOPE, VAR, STDEV, etc.) is
> a mathematically elegant, but numerically poor way to compute Least
> Squares estimates. I am well aware that LINEST will do poorly with
> numerically challenging data, but this is the first I have heard that
> Trendline on the graph may do better.

I don't guarantee it will always do better, but sometimes it does. They can get
entirely different answers on challenging data. And my experience is that Trendline
always wins. YMMV

The point is that LINEST teeters on the brink of numerical instability for quite
modest polynomial fit problems (N>3). The difficulty is that with high powers of X
values can mount up and then rounding errors break the matrix inversion routine
unless the problem is carefully scaled or you fit using functions more cunning than
powers of X. It makes no attempt to polish or check it's "solution".

You can stave off the numerical instability a bit by rescaling your problem. Judging
by the answers it gets I suspect that the chart trendline fit solves a rescaled
related problem and then converts the answer back into a polynomial. The simplest
quick fix for that original dataset is to fit to X-MeanX.

The only way to be sure that you have a genuine polynomial best fit solution with
LINEST is to explicitly test the alleged solution for being minimum least squares
(for N>4 there is a good chance it isn't)...

Regards,
Martin Brown

[Ken Wright]

Thanks guys, appreciate the comments. Basically what I had was a set of 5
year end points given to me and I wanted a best fit line that would curve in
a way I would expect it to. When I straight lined each data point from one
to the other and then graphed the data and added a trendline I had a look at
the all the trendline options and the only one that looked like I was
expecting the data to be was the polynomial 6th order.

Data was
Year 1 Year 2 Year 3 Year 4 Year 5
50,000 110,000 220,000 330,000 400,000 - This is the only data
I was given, and it represents a takeup rate.

In the end I used a cubic function provided by someone else to calculate y
given that x was months 12,24,36,48,60 for the data above.
y = ax^3 + bx^2 + cx^1 + d. Calculated deltas to expected values, squared
them and then summed the squares using solver to minimise the function. I
got the graph I wanted and the points I expected, but would the functions
quoted have been easier given the explanation above?

Many thanks
Ken........................

"Martin Brown" <martin...@pandora.be> wrote in message

news:3D806ABB...@pandora.be...

[Jerry W. Lewis]

Interesting example. The condition number of X'X is about 10^30, which
suggests that quad precision would be needed to get anything meaningful
from MInverse(X'X)*X'y (used by LinEst). LinEst does not get a single
figure correct on any coefficient in this example, while Trendline's
coefficients are correct to roughly 9 significant figures.

The interesting questions are what Trendline is doing (orthogonal
polynomials?, Givens' rotations?, ...), and why Microsoft maintains two
algorithms when one is clearly superior? Unfortunately, this is not the
only instance of the latter

http://groups.google.com/groups?selm=3BB1DAEC.3080705%40mediaone.net

It does suggest that comparing LinEst and Trendline coefficients might
be a good test for whether the dataset is numerically challenging.

Jerry