Trendlines

[Josh]

I used the trendline feature in excel and recieved an equation of "y =
3E+09x6 - 7E+08x5 + 8E+07x4 - 4E+06x3 + 42077x2 + 1197.x"

What do thhe Plus signs mean after the E (presumably the E means times ten
to the ____). Also, what does the decimal mean before the x (the last term)

 

[Joel]

below is the polynomial that represent the graph. Where ** means Raised to
the Power. x represent the x-axis on the graph. Below I tried to break out
the formula so you can understand it better. If you take a value on the
x-axis a added to the formular you will get approximately the value on the
y-axis

10**9 = 10 x 10 x 10 x 10 x 10 x 10 x 10 x 10
3*(10**9) = 3000000000 : a 3 followed by 9 zeros
-7*(10**8) = -700000000 : -7 dollowed by 8 zerro

x is the value on the x-axis of the graph
x**6 = (x)(x)(x)(x)(x)(x) = x multiplied by itself 6 times
x**5 = (x)(x)(x)(x)(x) = x multiplied by itself 5 times

This is the actuall polynomial represented by the graph using the above
notation
3*(10**9)(x**6) +
-7*(10**8)(x**5) +
8*(10**7)(x**4) +
-4*(10**6)(x**3) +
42077(x**2) +
1197x

 

[Del Cotter]

below is the polynomial that represent the graph. Where ** means Raised to
the Power. x represent the x-axis on the graph. Below I tried to break out
the formula so you can understand it better. If you take a value on the
x-axis a added to the formular you will get approximately the value on the
y-axis

10**9 = 10 x 10 x 10 x 10 x 10 x 10 x 10 x 10
3*(10**9) = 3000000000 : a 3 followed by 9 zeros
-7*(10**8) = -700000000 : -7 dollowed by 8 zerro

You didn't explain what the plus sign meant. All it means is that it
could have been a minus instead. A minus makes the E mean "divided by
ten to the ____", instead of "times ten to the ____"

1E+2 = 1*10^2 = 100
1E+1 = 1*10^1 = 10
1E+0 = 1*10^0 = 1
1E-1 = 1/10^1 = 0.1
1E-2 = 1/10^2 = 0.01

The numbers can be formatted so that the plus sign doesn't appear, and
the minus sign only appears if there actually is a need for it. That
would be a better format for displaying polynomial equations in
trendlines. Just right click on the trendline equation, select "Format
Data Labels" (that's just the trendline equation they're talking about)
and make the required changes.

I haven't got an explanation for the decimal, as I can't get it to do
that.

 

[Joel]

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.

 

[Del Cotter]

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.

 

[Josh]

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!

 

[David Biddulph]

The equation you gave was a polynomial in x, with no constant term, so by
definition it must pass through the origin (0,0).

 

[Del Cotter]

Thanks for your help. This is all very helpful, but I still end up with my
original problem.

You might have stated your original problem, if that was what you wanted
help with. The entire text of your original post was:

I used the trendline feature in excel and recieved an equation of "y =
3E+09x6 - 7E+08x5 + 8E+07x4 - 4E+06x3 + 42077x2 + 1197.x"

What do thhe Plus signs mean after the E (presumably the E means times ten
to the ____). Also, what does the decimal mean before the x (the last term)

For your problem with the graphing calculators, try clicking on the
trend equation, then increasing the number of decimal points it
displays. You are currently reading off an equation whose parameters
have only *one* significant figure in the x3 and higher terms. No wonder
typing that into your graphing calculator produces a different curve!
How do you know that the true equation is not

2.6e9 x6 - 6.6e8 x5 + 7.6e7 x4 - 3.6e6 x3 + 42077 x2 + 1197 x

which the equation above would be a valid one-significant-figure
approximation of, and yet would probably produce a radically different
actual curve?

If that still doesn't help, I would abandon the "trendline facility" in
the Excel spreadsheet's charting feature, and trust the graphing
calculators instead. If the calculators can't do what you want, and you
want to use Excel instead, then use Excel as a spreadsheet, not as a
graph. Create the trend in the spreadsheet as a series of cells, and
calculate the area under the curve likewise.

The following two newsgroups:

microsoft.public.excel.misc
microsoft.public.excel.worksheet.functions

should be able to give you advice on doing that, but this forum,
microsoft.excel.public.charting, can't help you until you've found the
numbers you want. We are a graphical display newsgroup, not a
mathematical methods newsgroup. Once you've got the functions you are
looking for, then we can help you present them in a pleasing visual
fashion.

You've fallen into the trap, created by Microsoft's misguided addition
of a trendline facility into the chart feature of the Excel spreadsheet
program, of thinking of Excel as a graph program with a spreadsheet
feature. A lot of people get into that mindset even without the
trendline feature being to blame.

 

[Jerry W. Lewis]

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/microsoft.public.excel/msg/969a2bb33e6cdbb8
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]

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]

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