Regards
Ken............................
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
"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
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:
>
...
Amen!
-
Thomas Bartkus
www.BartkusConsulting.com
"Martin Brown" <martin...@pandora.be> wrote in message
news:3D7EF6BA...@pandora.be...
>
>
"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
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...
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