Polynomial trend line equation values?

busarider29

New Member
Joined
Dec 4, 2016
Messages
2
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
... "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)
 
Last edited:
Upvote 0
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
 
Upvote 0
You are welcome.

If you are happy with copying and pasting "variables" from the trendline label, then you found the right solution.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,196,409
Messages
6,015,105
Members
441,870
Latest member
kojack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top