G

#### Guest

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)

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

G

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)

Ad

G

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

D

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.

G

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.

D

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.

G

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!

Ad

D

definition it must pass through the origin (0,0).

D

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:

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.

G

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

G

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.

Ad

G

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

Ad

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.