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,B1

9).

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