LINEST for specifying polynomial regression function

The first thing I would do is define exactly what this "polynomial" equation will look like. If I understand correctly, you have 5 independent variables. Multiply that by every possible combination of 4 exponents (0,1,2,3) for a cubic polynomial, and you have a lot of possible terms. Once you define the desired terms for the regression equation, then it will be easier to build the regression matrix for the LINEST() function.

Does your math education extend to linear algebra? The "linear" in linear regression/LINEST() refers to linear functions in this sense. All polynomials are "linear" functions when looked at in this way, and can, therefore, be regressed using the LINEST() function. (To within the limitations of number of predictor variables and numerical stability of the matrix inversion algorithms). Once you specify a polynomial, you should be able to build a regression matrix based on that polynomial, then use the LINEST() function to regress the parameters for that polynomial.

In cases where you are not sure which polynomial to use, Excel does not have a utility for determining which equation to use. To choose a form for the polynomial, you may need to run through a sequence like: a) choose an equation, b) regress parameters using LINEST(), c) measure and record "goodness of fit", d) choose a different equation and repeat, e) compare your "goodness of fit" scores and decide which form of the polynomial provided the "best" fit.

I know a lot of times people try to create the regression matrix inside of the LINEST() function. That is fine for them, but I personally prefer to create the regression matrix in the spreadsheet before sending it to the LINEST() function.

Example in attached spreadsheet.

What leads you to believe a polynomial regression is appropriate for this, aside from that a polynomial with enough terms will match anything?

I see you have entered the formula in your spreadsheet by selecting cells R3:W4. I am having difficulty in understand the array selection for the formula input? Is it 6 columns long to reflect the 5 variables plus the intercept? I had previously thought that 5 rows x 2 columns was required to input the formula.

Row 3 is technically not a part of the LINEST() function -- in row 3 I have merely entered text labels so I know which cell contains which parameter.

The reason for selecting a 1row by 6column array for the function output is that I have 6 parameters: the 5 "m" parameters + the final constant b, so you understood this correctly. LINEST() always outputs one more value than there are columns in the known_x matrix. You can force b to be 0 by putting FALSE in the third argument, but it is still part of the output. I'm not sure how many terms/parameters you are expecting to use in your final equation, so this may need to be larger (or smaller, though LINEST() will output N/A if you select a larger array that it actually outputs) depending on the actual equation you are going to use.

I'm not sure where you got the idea that LINEST()'s output was in column. To my knowledge, LINEST() has always output a horizontal array. Nested inside the TRANSPOSE() function, you can get it to output a vertical array [=TRANSPOSE(LINEST(...))] if you want.

Yes, you can access the additional statistics by adding the fourth argument = TRUE. The help file for the LINEST() function: http://office.microsoft.com/en-us/ex...in=HA010277524 The arrangement of the output matrix is shown in a table near the bottom of this page.

I have no idea how to make a best selection. In addition to the single-regressor terms, there are potentially all of the cross terms. Just for squares (coefficients omitted),

y =
A^0 B^0 C^0 + A^1 B^0 C^0 + A^2 B^0 C^0 +
A^0 B^1 C^0 + A^1 B^1 C^0 + A^2 B^1 C^0 +
A^0 B^2 C^0 + A^1 B^2 C^0 + A^2 B^2 C^0 +

A^0 B^0 C^1 + A^1 B^0 C^1 + A^2 B^0 C^1 +
A^0 B^1 C^1 + A^1 B^1 C^1 + A^2 B^1 C^1 +
A^0 B^2 C^1 + A^1 B^2 C^1 + A^2 B^2 C^1 +

A^0 B^0 C^2 + A^1 B^0 C^2 + A^2 B^0 C^2 +
A^0 B^1 C^2 + A^1 B^1 C^2 + A^2 B^1 C^2 +
A^0 B^2 C^2 + A^1 B^2 C^2 + A^2 B^2 C^2

That's 27 possible coefficients (including the constant term) to regress 21 points if there were only three regressors. Increasing that to 7th order and your five regressors gives you a choice of 8^5 -1 (32767) terms, from which you could choose at most 20 (plus the constant term). More choices than the number of quarks in the universe (maybe).

Your solution, using a little different approach that lets you explore the terms, is attached.

a polynomial with enough terms will match anything?

I'm reminded of a saying that gets kicked around our office a lot. Wikiquotes attributes it to John von Neumann. "With four parameters I can fit an elephant. With five I can make him wiggle his trunk." http://en.wikiquote.org/wiki/John_von_Neumann

Whenever we talk about high order polynomials as fitting equations, there does come a point where we get concerned with "overfitting" the data. I'm not sure exactly how one judges "adequate fit" vs "overfit" -- I'm not enough of a statistician.

I have heard of cases where a researcher/mathematician would start with an "excess" of parameters in their modeling equation. Then, through a series of statistical tests, would identify which terms/parameters were most significant and which were less significant in predicting the dependent variable. I'm not enough of a statistician to talk about the statistical tests that would be used. Excel doesn't have them pre-programmed in (though I expect they could be programmed into Excel by someone who was familiar with the statistical algorithms), and I would not be surprised to find no applications with those algorithms pre-programmed in.

Originally Posted by MrShorty

"With four parameters I can fit an elephant. With five I can make him wiggle his trunk."

:ROFL: I have never heard that.