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