multiple polynomial regression in Excel

[icystorm]

First, please excuse me if this is the wrong newsgroup for posting
this question... If so, kindly direct me on. Thanks!

In Excel, it is easy to create the coefficients for muliple linear
regression and multiple non-linear regression by using the LINEST and
LOGEST functions to produce an array of statistics that contain the
coefficients dervived from the x, y data set. I use these functions
routinely and they work well.

My question is this:

Is Excel able to create an array of statistics, similar to LINEST and
LOGEST, that contains the coefficients for an array of multiple x
values (independent variables) and, of course, the single column of y
values (dependent variables)?

Thanks kindly!

Joseph

 

[icystorm]

My question is this:

Is Excel able to create an array of statistics, similar to LINEST and
LOGEST, that contains the coefficients for an array of multiple x
values (independent variables) and, of course, the single column of y
values (dependent variables)?

I botched my question, leaving out the most important part. Let me
rephrase...

Is Excel able to create an array of statistics, similar to LINEST and
LOGEST, that contains the coefficients for an array of multiple x
values (independent variables) and, of course, the single column of y
values (dependent variables) that can be used for predicting the value
of Y in an Nth order polynomial.

In other words, what is the function for creating the statistics with
coefficients for muliple polynomial regression?

Thank you.

Joseph

 

[Mike Middleton]

icystorm -

what is the function for creating the statistics with coefficients for
muliple polynomial regression? <

LINEST

Multiple polynomial regression is a special case of multiple regression.

You can array-enter LINEST directly, e.g., =LINEST(y,x^{1,2,3},1,1),
substituting your data ranges for y and x, or you can use INDEX to get
specific values of the output array, as described by John Walkenbach at

http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

For predictions, you can use the coefficients from LINEST in worksheet
formulas, or you can use array-entered TREND directly, e.g.,
=TREND(knownY,knownX^{1,2,3},newX^{1,2,3},1).

- Mike
http://www.MikeMiddleton.com

My question is this:

Is Excel able to create an array of statistics, similar to LINEST and
LOGEST, that contains the coefficients for an array of multiple x
values (independent variables) and, of course, the single column of y
values (dependent variables)?

I botched my question, leaving out the most important part. Let me
rephrase...

Is Excel able to create an array of statistics, similar to LINEST and
LOGEST, that contains the coefficients for an array of multiple x
values (independent variables) and, of course, the single column of y
values (dependent variables) that can be used for predicting the value
of Y in an Nth order polynomial.

In other words, what is the function for creating the statistics with
coefficients for muliple polynomial regression?

Thank you.

Joseph

 

[icystorm]

On Aug 28, 6:58 pm, "Mike Middleton" <[email protected]>
wrote:

Thanks for the excellent response, Mike. I've been to the site you
gave and used LINEST in that way before to construct a 6th-order
polynomial trendline. It worked great. But...

For predictions, you can use the coefficients from LINEST in worksheet
formulas...

This is pertains to what I was really asking...

What is the excel formula for creating a prediction for Y based on the
coefficients given in the muliple polynomial regression statistics
produced by LINEST()?

For a 3rd order polynomial, I think it is:

y = (m1 * x1^3) + (m2 * x2^2) + (m3 * x3) + b

where:

m1 = coefficient for x1
m2 = coefficent for x2
m3 = coefficient for x3
b = y-intercept

Also, Mike, the formula...

LINEST(y,x^{1,2,3},1,1)

....will produce statistics for a 3rd order polynomial, correct? I can
only get this to work correctly if I have 3 columns of x variables
(e.g., it seems that the order of the polynomial (i.e., 3rd, 4th, 5th,
etc.) must match the number of columns of x variables). But what if I
want to produce a 6th order polynomial for the same 3 columns of x
variables? I receive a #VALUE! error.

I think I have missed some important point over the years with using
LINEST in this way. Thanks for any clarification you can provide.

Cheers,
Joseph

 

[Mike Middleton]

Joseph or icystorm -

What is the excel formula for creating a prediction for Y ... <

Excel Help for "linest function" says

"you can use LINEST to calculate a range of other regression types by
entering functions of the x and y variables as the x and y series for
LINEST. For example, the following formula:
=LINEST(yvalues, xvalues^COLUMN($A:$C))
works when you have a single column of y-values and a single column of
x-values to calculate the cubic (polynomial of order 3) approximation of the
form:
y = m1*x + m2*x^2 + m3*x^3 + b"

That example uses COLUMN($A:$C) instead of {1,2,3}.

I can only get this to work correctly if I have 3 columns of x variables <

The example uses a single column of X values. The values for X^2 and X^3 are
not explicitly needed on the worksheet.

If you had nineY values in A1:A9 and the corresponding values for X, X^2,
and X^3 in columns B, you would use =LINEST(A1:A9,B19).

The "shortcut" would use =LINEST(A1:A9,B1:B9^{1,2,3}).

REGRESSION OVERFIT: In my experience with curve fitting, I have never had a
reason to use more than quadratic (X and X^2) for single-bulge data patterns
or cubic (X and X^2 and X^3) for S-shaped patterns. Before you use
higher-order polynomials, I suggest studying some of the results of a Google
search for "regression overfit."

... if I have 3 columns of x variables ... <

I'm not sure I understand your situation. If you have a single X variable,
the previous discussion applies for fitting polynomials based on that single
X variable. Alternatively, if you have multiple unrelated X variables and if
you want to model one or more using polynomials, you will have to enter the
higher-order values on the worksheet (in adjacent columns), e.g., X1, X1^2,
X2, X2^2, X2^3, X3, etc.

- Mike
http://www.MikeMiddleton.com



On Aug 28, 6:58 pm, "Mike Middleton" <[email protected]>
wrote:

Thanks for the excellent response, Mike. I've been to the site you
gave and used LINEST in that way before to construct a 6th-order
polynomial trendline. It worked great. But...

For predictions, you can use the coefficients from LINEST in worksheet
formulas...

This is pertains to what I was really asking...

What is the excel formula for creating a prediction for Y based on the
coefficients given in the muliple polynomial regression statistics
produced by LINEST()?

For a 3rd order polynomial, I think it is:

y = (m1 * x1^3) + (m2 * x2^2) + (m3 * x3) + b

where:

m1 = coefficient for x1
m2 = coefficent for x2
m3 = coefficient for x3
b = y-intercept

Also, Mike, the formula...

LINEST(y,x^{1,2,3},1,1)

....will produce statistics for a 3rd order polynomial, correct? I can
only get this to work correctly if I have 3 columns of x variables
(e.g., it seems that the order of the polynomial (i.e., 3rd, 4th, 5th,
etc.) must match the number of columns of x variables). But what if I
want to produce a 6th order polynomial for the same 3 columns of x
variables? I receive a #VALUE! error.

I think I have missed some important point over the years with using
LINEST in this way. Thanks for any clarification you can provide.

Cheers,
Joseph