Resolving Polynomial Trendline Formula for Chart

  • Thread starter Thread starter soccerkatie
  • Start date Start date
S

soccerkatie

How would the following Chart equation be resolved in Excel:

y = 147832x2 - 150195x + 2E+07

Thanks!
 
Do you mean the x-value(s) for which y = 0? (hence, the intersection(s) with
the horizontal axis).
 
Thanks - "resolved" is a very porr choice of words. I just would like to use
the equation to produce the "next value" of y given x. Guess what I'm really
thrown by what does "E" represent in the equation provided (below)?

Thanks for your patience
 
In 2E07, the E stands for 10 while the 07 is taken to be a power
So it means 2 × 10(power of 7) (or 20 million)
Have you not seen E used like this on a hand-held calculator?
best wishes
 
Hi Katie,

Check out this site for the equations of Trendlines.
http://j-walk.com/ss///excel/tips/tip101.htm

Here is an example using the equations for 2nd order polynomial.

Say your X data is in A1:A10 and your Y data is in B1:B10

Put these formulae in place
In D1 put =INDEX(LINEST(B1:B10,A1:A10^{1,2}),1)
In E1 put =INDEX(LINEST(B1:B10,A1:A10^{1,2}),1,2)
In F1 put =INDEX(LINEST(B1:B10,A1:A10^{1,2}),1,3)

Now in say D3 put your given X value.
This formula in E3 will then return the corresponding Y value
=(D1*D3^2)+(E1*D3)+F1
this is just the formula that your chart has displayed using the
calculated references. (In the form y = ax^2 + bx + c)

HTH
Martin
 
Thanks, MartinW, that was very helpful!

One thing I don't understand, though, is the formula that is displayed on
the chart for a trend line. Why are the coefficients in the displayed formula
not the same as the ones obtained with LINEST()? I thought maybe the
displayed formula used arbitrarily assigned X values starting with 0, but
that doesn't seem to be the case.

David Reynolds
 
Hi David,

The displayed equation should be the same as the calculated values.
Are you sure that the values aren't just looking different because they have
less decimal places? You can change the decimals in the chart by
right-clicking
on the equation and Format Data Labels>Number tab and set the number
decimals to 14.

If they definitely are different then you will need to post more detail
here.
Include your X and Y data and also the equation it is showing. We will
need enough info to recreate your chart.

Alternatively you can upload a sample spreadsheet at this site
http://www.savefile.com/
And then post a copy of the Link back here. If you are using 2007
you will need to save it as type 97-2003 first.

HTH
Martin
 
Whoops, it wasn't the number of decimals, but I found my mistake. The
displayed formula coefficients do match now.

Thanks,
David
 
Back
Top