Display Coefficients of a Polynomial Trendline

[bmook]

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

 

[ShaneDevenshire]

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.

 

[Mike Middleton]

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]

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

-B

 

[Travis]

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)