trendline equation
 

We have XY (scatter-connected by line) chart.
When I use trendline (polynomial, order of 2) I can ask show up the equation.

But now we would like extract all number from equation shown in individual
trendline, and put into a separate file, so we can do detail and complex
statistical analysis.
In linear trandline, I can extract number by just computing slope, and
intercept.

Can anyone tell me how to extract number of polynomial equation?

trendline equation
 

You can use LINEST for this. Check Bernard Liengme's site:

http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Terry" wrote in message
...
We have XY (scatter-connected by line) chart.
When I use trendline (polynomial, order of 2) I can ask show up the
equation.

But now we would like extract all number from equation shown in individual
trendline, and put into a separate file, so we can do detail and complex
statistical analysis.
In linear trandline, I can extract number by just computing slope, and
intercept.

Can anyone tell me how to extract number of polynomial equation?

trendline equation
 

Sorry it seems I did not explain well.
I only get 1 number from LINEST, and that is not what I want.

Use this for example.
After I ask for equation, (polynormial, second order).
Excel calculate equation and give me
y = 0.0005 x^2 - 0.4797 x + 131.051
The number I would like to extract are 0.0005, -0.4797, and 131.051
Those 3 number with 50 more (group of 3) from other data set will import to
SAS for other detail analysis.

LINEST I can only get 0.0005.
What I miss?
How can I get other numbers?

trendline equation
 

Terry -

Maybe you missed Bernard's instructions for "array-entering" the function.

So, to get all of the functions output, select a range of 5 rows by 3
columns, type the function =LINEST(Yrange,Xrange^{1,1},1,1) but don't press
Enter. Instead, hold down the Shift and Control keys while you press Enter.

If you are interested only in the coefficients, you could select a range of
1 row by 3 columns before array-entering the function.

- Mike
http://www.mikemiddleton.com

"Terry" wrote in message
...
Sorry it seems I did not explain well.
I only get 1 number from LINEST, and that is not what I want.

Use this for example.
After I ask for equation, (polynormial, second order).
Excel calculate equation and give me
y = 0.0005 x^2 - 0.4797 x + 131.051
The number I would like to extract are 0.0005, -0.4797, and 131.051
Those 3 number with 50 more (group of 3) from other data set will import
to
SAS for other detail analysis.

LINEST I can only get 0.0005.
What I miss?
How can I get other numbers?

trendline equation
 

That would be

=LINEST(Yrange,Xrange^{1,2},1,1)

array-entered.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Mike Middleton" wrote in message
...
Terry -

Maybe you missed Bernard's instructions for "array-entering" the function.

So, to get all of the functions output, select a range of 5 rows by 3
columns, type the function =LINEST(Yrange,Xrange^{1,1},1,1) but don't
press Enter. Instead, hold down the Shift and Control keys while you press
Enter.

If you are interested only in the coefficients, you could select a range
of 1 row by 3 columns before array-entering the function.

- Mike
http://www.mikemiddleton.com

"Terry" wrote in message
...
Sorry it seems I did not explain well.
I only get 1 number from LINEST, and that is not what I want.

Use this for example.
After I ask for equation, (polynormial, second order).
Excel calculate equation and give me
y = 0.0005 x^2 - 0.4797 x + 131.051
The number I would like to extract are 0.0005, -0.4797, and 131.051
Those 3 number with 50 more (group of 3) from other data set will import
to
SAS for other detail analysis.

LINEST I can only get 0.0005.
What I miss?
How can I get other numbers?

trendline equation
 

Same data set are used, but my boss like to see the trendline equation
(non-liner Exponential)
I think the equation for Exponential is y = B*e ^(Ax)

Will =LINEAT works to extract the number (B and A)?
If so, what is the set up?
If not, what will be the best way to extract the numbers?

"Terry" wrote:

We have XY (scatter-connected by line) chart.
When I use trendline (polynomial, order of 2) I can ask show up the equation.

But now we would like extract all number from equation shown in individual
trendline, and put into a separate file, so we can do detail and complex
statistical analysis.
In linear trandline, I can extract number by just computing slope, and
intercept.

Can anyone tell me how to extract number of polynomial equation?

trendline equation
 

Take logarithms on both sides to get
LN(y) = LN(B)+A*x
for which you can use LINEST or the SLOPE and INTERCEPT functions.

Jerry

"Terry" wrote:

Same data set are used, but my boss like to see the trendline equation
(non-liner Exponential)
I think the equation for Exponential is y = B*e ^(Ax)

Will =LINEAT works to extract the number (B and A)?
If so, what is the set up?
If not, what will be the best way to extract the numbers?

"Terry" wrote:

We have XY (scatter-connected by line) chart.
When I use trendline (polynomial, order of 2) I can ask show up the equation.

But now we would like extract all number from equation shown in individual
trendline, and put into a separate file, so we can do detail and complex
statistical analysis.
In linear trandline, I can extract number by just computing slope, and
intercept.

Can anyone tell me how to extract number of polynomial equation?