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:D, you would use =LINEST(A1:A9,B1:D9).

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



"icystorm" wrote in message
...
On Aug 28, 6:58 pm, "Mike Middleton"
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

whe

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