Polynomial trend line equation values?

[busarider29]

Hello-

I am very green in Excel and my skills are limited, so pardon me if this question appears very non-trivial. I have two rows of data and I've created a trend line with 2nd order polynomial. I created the spreadsheet with data a while back so I don't recall all of the steps to do it. Anyway, I've changed some of the data today, which of course has changed my polynomial equation. The equation appears like so - "y = 1E-07x2 + 0.3592x + 10.68". I need to know what the actual values are of the first two variables of the equation down to the least significant digit, or as far to the right as excel will show.

Thank you,
D

 

[Tetra201]

... "y = 1E-07x2 + 0.3592x + 10.68". I need to know what the actual values are of the first two variables of the equation...

Did you mean coefficients?

If yes, use the following formulas:

=INDEX(LINEST(your_y_values,your_x_values^COLUMN($A:$B)),1,1)
=INDEX(LINEST(your_y_values,your_x_values^COLUMN($A:$B)),1,2)

 

[busarider29]

Did you mean coefficients?

If yes, use the following formulas:

=INDEX(LINEST(your_y_values,your_x_values^COLUMN($A:$B)),1,1)
=INDEX(LINEST(your_y_values,your_x_values^COLUMN($A:$B)),1,2)

No, not exactly although I will play around with those formulas. Actually, I figured it out and as I thought, it was quite easy. In the "Format TrendLine Label" options box, I simply needed to select "Scientific" (was set at "General") and enter 6 in the "Decimal Places" field.

Thanks,
D

 

[Tetra201]

You are welcome.

If you are happy with copying and pasting "variables" from the trendline label, then you found the right solution.

 

[joeu2004]

I figured it out and as I thought, it was quite easy. In the "Format TrendLine Label" options box, I simply needed to select "Scientific" (was set at "General") and enter 6 in the "Decimal Places" field.

Might as well format with 14 decimal places if you want the coefficients "down to the least significant digit, or as far to the right as excel will show".

FYI, the correct LINEST usage is to select 3 horizontal cells (e.g. A1:C1) and array-enter (press ctrl+shift+Enter, not just Enter) the following paradigm:

=LINEST(Y1:Y10, X1:X10^{1,2})

Then you can use =SERIESSUM(X1,2,-1,$A$1:$C$1) to generate the estimated-Y for X1.

Use =SERIESSUM(X1,2,-1,$A$1:$B$1)+$C$1 if X1 might be zero.

You might notice a difference between the LINEST and chart trendline coefficients. Usually, the difference is small.

If they are very different, be sure that the chart type is not a Line chart. You should use XY charts for trendlines.