Get polynomial regression coefficients excel

**DionApp** · 09-08-2021, 08:36 AM

Hi,

I want to reproduce the regression coefficients one by one without using the scatterplot.

I did this allready for the 1st order regression but want to do this as-well for the 3d order regression. Can anyone tell me how to calculate each coefficient individual?

For example:

Kolom A Kolom B
2 0,5
10 1,2
21 3,2
25 5
41 5,5
41 9
43 9,3
51 9,8
55 11
65 10
67 11
82 11,9
86 12
86 14,2
90 15
93 15,6

1st order = 0,1532x + 0,7947
where 0,1532 is the intercept and 0,7947 is the slope of the line
which can be calculated with =INTERCEPT and =SLOPE.

3d order is 0,00001x^3-0,0027x^2+0,2977x-0,9103
How can I calculate those individual?

**AliGW** · 09-08-2021, 08:50 AM

There is no 2020 version of Excel - if you have MS365, please update your profile.

**MrShorty** · 09-08-2021, 09:30 AM

You can use the LINEST() function to get the coefficients of any linear* function, including polynomials. The helpfile shows an example for a 3rd order polynomial (https://support.microsoft.com/en-us/...a-fa7abf772b6d towards the end of the Remarks section just before the Examples section).

Something like =LINEST(known_y's,known_x's^{1,2,3,4}) for a 4th order polynomial.
Something like =LINEST(ln(known_y's),ln(known_x's)) will give you a power trendline [ln(y)=m*ln(x)+b]
Other variations are possible, the only limitation is the your regression equation must be linear or transformable into something linear*.

* -- Note that for our purposes here, linear is much more than straight lines, but refers to the definition of linear function that you would learn in linear algebra.