Excel Formula to regression fit Cubic polynomial

[vsennak]

Hi,

I am using the following function to do the curve fitting.

=INDEX(LINEST($G9:$G22,$F9:$F22^{1,2,3}),1,1)

It works most times but not for certain data.
And there is a mismatch between the trendline (polynomial cubic) and the values returned by the function for this data set.

Attached is a sample data set.
Also, whenever this happens, the C term or coefficient of X term goes to zero from function calculation.

Any pointers to the issue, most welcome.

Thank you in advance.

[shg]

You're doing the regression four times, which is Excel abuse. Select I34:L34, paste

=LINEST($G9:$G22,$F9:$F22^{1,2,3})

... in the formula bar, and confirm with Ctrl+Shift+Enter.

For whatever reason, charts and LINEST do polynomial regression differently. The chart gives R2 of 0.9843, LINEST 0.9697

[lori_m]

For whatever reason, charts and LINEST do polynomial regression differently. The chart gives R2 of 0.9843, LINEST 0.9697

The reason is to do with how Excel handles collinearity, according to MS Help:

"...The LINEST function checks for collinearity and removes any redundant X columns from the regression model when it identifies them. Removed X columns can be recognized in LINEST output as having 0 coefficients in addition to 0 se values...."

Essentially the problem is that forming a polynomial basis of the x values leads to linear combinations of values that cannot be distinguished given the 15 digit precision. To correct for this center the data around the mean by replacing x by (x-average(x)):

=LINEST(G9:G22,(F9:F22-AVERAGE(F9:F22))^{1,2,3},1,1)

This gives the same x³ value and R² value as the chart trendline.

To recover the other coefficients post-multiply by the matrix of binomial terms, i.e. enter using CTRL+SHIFT+ENTER in a 4 column range:

=MMULT(LINEST(G9:G22,(F9:F22-AVERAGE(F9:F22))^{1,2,3}),IFERROR(COMBIN({3;2;1;0},{3,2,1,0})*(-AVERAGE(F9:F22))^({3;2;1;0}-{3,2,1,0}),0))

[vsennak]

Hi Lori_m,

Thanks for the reply.

Was able to use the first function and get X^3 term, but couldn't figure out how to use the 2nd equation.
Can you pls explain what Ctrl+Shift+Enter in the 4 column range means and how to use/key-in the second equation.

Thank you.

[vsennak]

Thanks Lori_m and shg, got it working.