Trendlines

Sorry if I was not clear. I did NOT recommend using a higher degree
polynomial (which in theory could be fit using LINEST), I expressed concern
that a 6th degree polynomial may already be too high, both practically and
numerically.

If your x data only spans the range 0 to 0.1, then fitting a 6th degree
polynomial is a difficult numerical problem. Note that the numerical
difficulty is determined mainly by the x data, not the y data.

Jerry

"Josh" wrote:

Thanks for your help guys. Just to respond to a few of the questions/comments
to clear up a few ambiguities.
1) I have been using XY scatter
2) I am currently using 10 significant figures after the decimal, I will try
using 14, although I doubt that will make a huge difference
3) In response to Jerry Lewis' recommendation that I use a higher degree
polynomial, I believe Excel only goes up to the 6th degree (at least as far
as I can tell)
4) The data goes from 0 to .1, but its very random (it has a lot of
intermittent zeros scattered throughout it), but that doesn't explain why
what I'm seeing on the graphing calculator does not equate with what I am
seeing as the trendline. Thanks for all of your help guys. I am going to try
to jump around this problem and see if I can find a way for excel to find an
area under the curve of the excel line using one of the above recommended
forums.

"Jerry W. Lewis" wrote:

Several random comments:

Are you using an "XY (Scatter)" chart? If not, the fitted equation is
probably meaningless. Users are often fooled by the name and location in the
charting menu into using a "Line" chart, which will substitute 1,2,3,... for
your x values.

Display the coefficients in scientific notation with 14 decimal places. As
Del Cotter noted, the default display on the chart gives too few figures to
be of any use in calculating the polynomial. You can also fit polynomials
with LINEST, but beware if LINEST results do not match the chart trendline.

Are you sure that you have not overfit the data? You can always improve the
fit to the existing data by adding more polynomial terms. That does not mean
that the "improved" fit is better for interpolation between or extrapolation
beyond the existing data--you may just be chasing noise in the data.

What range is spanned by your x data? Fitting a sixth degree polynomial can
be a very difficult numerical problem, particularly if the x data does not
span a wide enough range.
http://groups.google.com/group/micro...9a2bb33e6cdbb8
gives an example that looks innocuous on the surface, but is so difficult
numerically, that a straightforward implementation of the theoretical
calculations for least squares estimates (such as was in LINEST prior to
Excel 2003) would give no correct figures for the estimated coefficients.
The chart trendline is actually quite good numerically, and was able to get 9
correct figures (better than most dedicated statistics packages). I can't
comment on the quality of the fitting algorithm in your graphics calculator.

Jerry

"Josh" wrote:

Thanks for your help. This is all very helpful, but I still end up with my
original problem. When I use the equation generated by excel and type it into
a graphing utility (such as a TI-83, TI-89, or online graphing calculator) I
continually receive a different graph than the one excel provides. The graph
I excel gives does not cross the x axis between 0 and .1 but when I type it
into one of the above stated graphing utilities it does. I would be more than
happy to send you the excel spread sheet via email if you think you may
understand how to solve this problem. Thanks a lot.

Also, does anybody know if there is an "area under the curve" function in
excel. That would solve ALL of my problems.

Thanks for your help so far!

"Del Cotter" wrote:

On Sun, 18 Mar 2007, in microsoft.public.excel.charting,
Joel said:

The decimal point is simply a decimal point. The graph is using large values
of Y so the digits right of the decimal point are insignificant. Other
charts for Y values closer to 1 you would need better accuracy and decimal
point would be needed. For some reason Excel doesn't eliminate the decimal
point. it really should been 1197.0 or just 1197, not 1197.

Exactly, but I can't get it to display just the decimal point, as
happened to Josh. In my copy of Excel, it *does* eliminate the decimal
point if it isn't necessary.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.