Resolving Polynomial Trendline Formula for Chart
 

How would the following Chart equation be resolved in Excel:

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

Thanks!

Resolving Polynomial Trendline Formula for Chart
 

Please explain what you mean by "be resolved"
To get the trendline values into cells see
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"soccerkatie" wrote in message
...
How would the following Chart equation be resolved in Excel:

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

Thanks!

Resolving Polynomial Trendline Formula for Chart
 

Do you mean the x-value(s) for which y = 0? (hence, the intersection(s) with
the horizontal axis).


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Bernard Liengme" wrote:

Please explain what you mean by "be resolved"
To get the trendline values into cells see
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"soccerkatie" wrote in message
...
How would the following Chart equation be resolved in Excel:

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

Thanks!

Resolving Polynomial Trendline Formula for Chart
 

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

"Bernard Liengme" wrote:

Please explain what you mean by "be resolved"
To get the trendline values into cells see
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"soccerkatie" wrote in message
...
How would the following Chart equation be resolved in Excel:

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

Thanks!

Resolving Polynomial Trendline Formula for Chart
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"soccerkatie" wrote in message
...
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

"Bernard Liengme" wrote:

Please explain what you mean by "be resolved"
To get the trendline values into cells see
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"soccerkatie" wrote in message
...
How would the following Chart equation be resolved in Excel:

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

Thanks!

Resolving Polynomial Trendline Formula for Chart
 

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


"soccerkatie" wrote in message
...
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

"Bernard Liengme" wrote:

Please explain what you mean by "be resolved"
To get the trendline values into cells see
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"soccerkatie" wrote in message
...
How would the following Chart equation be resolved in Excel:

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

Thanks!

Resolving Polynomial Trendline Formula for Chart
 

Tushar Mehta also has nice material on this topic on his website:
http://www.tushar-mehta.com/excel/


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"MartinW" wrote:

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


"soccerkatie" wrote in message
...
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

"Bernard Liengme" wrote:

Please explain what you mean by "be resolved"
To get the trendline values into cells see
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"soccerkatie" wrote in message
...
How would the following Chart equation be resolved in Excel:

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

Thanks!

Resolving Polynomial Trendline Formula for Chart
 

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

Resolving Polynomial Trendline Formula for Chart
 

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 LabelsNumber 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





"CSX321" wrote in message
...
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

Resolving Polynomial Trendline Formula for Chart
 

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

Thanks,
David

Resolving Polynomial Trendline Formula for Chart
 

Glad to hear you got it working. Thanks for posting back.

Regards
Martin



"CSX321" wrote in message
...
Whoops, it wasn't the number of decimals, but I found my mistake. The
displayed formula coefficients do match now.

Thanks,
David