I am trying to display the coefficients of a trendline in cells. This way I
can reference the cells and if the data changes, the coefficients are
dynamically updated (as opposed to if the trendline was just displayed on the
chart and you had to manually update every time).

I am using the following formulas:

Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)

This works fine when I have the data set up in 2 columns
ex. Columns
X Y
1 5
3 6
5 7
but I can't seem to get the equations to work with the data set in rows
ex.
ROW
X 1 3 5
Y 5 6 7

The data is set in place based on some other stuff, so I'm trying to avoid
have to copy and transpose everything. Is there a way to incorporate the
TRANSPOSE function into the formulas? Or any other idea?

Thanks, any and all help is much appreciated!

-B

I think you can get what you want withou using INDEX

Select a range of 5 rows by 4 columns (we could increase or decrease the
column count later)
Type but do NOT enter you LINEST function (no INDEX)
Press Shift+Ctrl+Enter

Check out the resulting output. If you need to know what each item is
choose the help.
--
Thanks,
Shane Devenshire


"bmook" wrote:

I am trying to display the coefficients of a trendline in cells. This way I
can reference the cells and if the data changes, the coefficients are
dynamically updated (as opposed to if the trendline was just displayed on the
chart and you had to manually update every time).

I am using the following formulas:

Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)

This works fine when I have the data set up in 2 columns
ex. Columns
X Y
1 5
3 6
5 7
but I can't seem to get the equations to work with the data set in rows
ex.
ROW
X 1 3 5
Y 5 6 7

The data is set in place based on some other stuff, so I'm trying to avoid
have to copy and transpose everything. Is there a way to incorporate the
TRANSPOSE function into the formulas? Or any other idea?

Thanks, any and all help is much appreciated!

-B

bmook -

For data in rows, array-enter each:
=INDEX(LINEST(TRANSPOSE(known_y),TRANSPOSE(known_x )^{1,2}),1,1)
=INDEX(LINEST(TRANSPOSE(known_y),TRANSPOSE(known_x )^{1,2}),1,2)
=INDEX(LINEST(TRANSPOSE(known_y),TRANSPOSE(known_x )^{1,2}),1,3)

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel


"bmook" wrote in message
...
I am trying to display the coefficients of a trendline in cells. This way
I
can reference the cells and if the data changes, the coefficients are
dynamically updated (as opposed to if the trendline was just displayed on
the
chart and you had to manually update every time).

I am using the following formulas:

Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)

This works fine when I have the data set up in 2 columns
ex. Columns
X Y
1 5
3 6
5 7
but I can't seem to get the equations to work with the data set in rows
ex.
ROW
X 1 3 5
Y 5 6 7

The data is set in place based on some other stuff, so I'm trying to avoid
have to copy and transpose everything. Is there a way to incorporate the
TRANSPOSE function into the formulas? Or any other idea?

Thanks, any and all help is much appreciated!

-B

When your data is in rows, you need to use semi-colon instead of comma, when you raise the x to powers in the linest equation.

b=INDEX(LINEST(L6:N6,L5:N5^{1;2}),1,3)

On Tuesday, October 21, 2008 3:25 PM bmoo wrote:

I am trying to display the coefficients of a trendline in cells. This way I
can reference the cells and if the data changes, the coefficients are
dynamically updated (as opposed to if the trendline was just displayed on the
chart and you had to manually update every time).

I am using the following formulas:

Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)

This works fine when I have the data set up in 2 columns
ex. Columns
X Y
1 5
3 6
5 7
but I can't seem to get the equations to work with the data set in rows
ex.
ROW
X 1 3 5
Y 5 6 7

The data is set in place based on some other stuff, so I'm trying to avoid
have to copy and transpose everything. Is there a way to incorporate the
TRANSPOSE function into the formulas? Or any other idea?

Thanks, any and all help is much appreciated!

-B


On Tuesday, October 21, 2008 4:53 PM ShaneDevenshir wrote:

I think you can get what you want withou using INDEX

Select a range of 5 rows by 4 columns (we could increase or decrease the
column count later)
Type but do NOT enter you LINEST function (no INDEX)
Press Shift+Ctrl+Enter

Check out the resulting output. If you need to know what each item is
choose the help.
--
Thanks,
Shane Devenshire


"bmook" wrote:


On Tuesday, October 21, 2008 5:22 PM Mike Middleton wrote:

bmook -

For data in rows, array-enter each:
=INDEX(LINEST(TRANSPOSE(known_y),TRANSPOSE(known_x )^{1,2}),1,1)
=INDEX(LINEST(TRANSPOSE(known_y),TRANSPOSE(known_x )^{1,2}),1,2)
=INDEX(LINEST(TRANSPOSE(known_y),TRANSPOSE(known_x )^{1,2}),1,3)

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel


"bmook" wrote in message
...


On Tuesday, October 21, 2008 7:40 PM bmoo wrote:

Thanks Mike! I had tried to integrate the TRANSPOSE function this way
before, but was not using an array enter. Using the array enter worked
perfectly.

-B

"Mike Middleton" wrote: