Multivariable regression in Excell
Multivariable regression in Excell
(OP)
Does anyone have any suggestions how to do a multivariable polynomial (2nd and 3rd order) and power regressions in Excel and calculate the coefficients and some statistical characteristics of the fitted equation? Let's assume for simplicity only 2 independent variables.
The second question is if there is anyway to plot the 3D scattered points and the surface representing the regression equation of those points in Excel.
I found a solution for 2-varaible linear regression, but not for polynomial or power.
Thank you,
Dan
The second question is if there is anyway to plot the 3D scattered points and the surface representing the regression equation of those points in Excel.
I found a solution for 2-varaible linear regression, but not for polynomial or power.
Thank you,
Dan
RE: Multivariable regression in Excell
Polynomial regression can be treated as a special case of multivariate regression. Create data columns containing x^2, x^3, etc. Then treat these extra columns as if they are further independent variables.
(I have never quite rationalised in my own mind the extent to which it is statistically valid to treat x and x^n as independent, but this is the method used, and it seems to work.)
RE: Multivariable regression in Excell
Dan
RE: Multivariable regression in Excell
=LINEST(Y1:Y10,X1:X10^{1,2,3},TRUE,TRUE)
will fit a cubic polynomial
RE: Multivariable regression in Excell
Moe complicated may be a polynom of say 2nd order in x1 and 3rd order in x2.
Here, x1, x2..xn are the independent variables and y is the dependent variable.
Dan
RE: Multivariable regression in Excell
=LINEST(Y1:Y10,B1:B10^{1,2,3,0,0,0}*C1:C10^{0,0,0,1,2,3},TRUE,TRUE)
will fit first, second and third order in each independent variable.
Of course at some point you may decide the LINEST formula is too awkward and opt to have the powers in the worksheet.
When you have all variables in a table in the worksheet you can also use the matrix functions to perform the least squares regression as follows:
=MMULT(MINVERSE(MMULT(TRANSPOSE(A1:C5),A1:C5)),MMULT(TRANSPOSE(A1:C5),Y1:Y5))
which assumes you have the independent variables in a1:c5
RE: Multivariable regression in Excell
Thanks for help, it started to work. I tried the LINEST() function as you described it and got the coefficients for x1, x1^2, x1^3, x2, x2^2, x2^3, and the free one, but without any terms for products between the 2 independent variables (e.g.: x1*x2, x1^2*x2, x1^3*x2, etc.
I checkd the values, and it's not bad.
Attached is what I've got.
Dan
RE: Multivariable regression in Excell
=LINEST(Y1:Y10,B1:B10^{1,2,3,0,0,0,1,2,3}*C1:C10^{0,0,0,1,2,3,1,1,1},TRUE,TRUE)
RE: Multivariable regression in Excell
Thank you very much for your suggestion, it works. I also figured out how to get all the coefficents of all the other terms, following the pattern in your formula.
Now, I have to take care of the power regression, of the type y = a*(x1^b)*(x2^c). Any suggestions for this one?
Dan
RE: Multivariable regression in Excell
Generally, conversion to logarithmic variables works for power regression.
RE: Multivariable regression in Excell
RE: Multivariable regression in Excell
B1:B10^{1,2,3,0,0,0,1,2,3}*C1:C10^{0,0,0,1,2,3,1,1,1}
Call "x" the B column, and "z" the C column. This above form will give you the coefficients for an equation of form:
Y=a1*(x^1)*(z^0)+a2*(x^2)*(z^0)+a3*(x^3)*(z^0)+
a4*(x^0)*(z^1)+a5*(x^0)*(z^2)+a6*(x^0)*(z^3)+
a7*(x^1)*(z^1)+a8*(x^2)*(z^1)+a9*(x^3)*(z^1)+a10
where I am looking for a1, a2,...,a10. To get Excel to give me this coefficients a1, etc. I select cells 1 row by 10 columns (1 for each coefficient I want to output), type in
=LINEST(Y1:Y10,B1:B10^{1,2,3,0,0,0,1,2,3}*C1:C10^{0,0,0,1,2,3,1,1,1},TRUE,TRUE)
in the fx (function) box at the top, then hit CTRL-SHIFT-ENTER to get Excel to do the curve fitting for those coefficients. What I don't understand is the logic Excel uses for the output; in my 1 row by 10 column block of cells, where do the a1, a2, etc. go? Looks like 'a10', the constant term, always goes at the end. But it seems like it reverses the sequence of (remember, column B is x, C is z):
x^{1,2,3,0,0,0,1,2,3}*z^{0,0,0,1,2,3,1,1,1} so that the powers are reversed and the x and z are reversed. I will guess:
column coefficient
1 a9
2 a8
3 a7
4 a6
5 a5
6 a4
7 a3
8 a2
9 a1
10 a10 (the constant)
this doesn't seem to be the correct output sequence. I tried, for instance, just using only one term (say a2*x^2, plus the constant a10) to compute a sequence of 'Y', then curve fitting with =LINEST(Y,x^{1,2,3,0,0,0,1,2,3}*z^{0,0,0,1,2,3,1,1,1},TRUE,TRUE)
and the non zero coefficient kept jumping around in that 1x10 block I've selected in the worksheet.
RE: Multivariable regression in Excell
Cummings, unfortunatelly, for what I need, I cannot use a commercial package, thus, I'm stuck with Excel even for the non-linear regressions.
Thus, regarding power, [y=a*(X1)^b1*(X2)^b2], I used
LINEST(LN(A1:An),LN(B1:Cn),TRUE,TRUE), where y is in column A, X1 is in column B and X2 is in column C. The result gives ln(a), b1 and b2, thus, I had to take exp(ln(a)) to get the free term a. It seemss to work.
Regardding polynomial, I've got OK the 2nd order with all the 9 terms (y = A + B1*X1 + B2*X1^2 + C1*X2 + C2*X2^2 + D1*X1*X2 + D2*X1^2*X2 + E1*X1*X2^2 + E2*X1^2*X2^2), using:
LINEST(A1:An),B1:Bn^{1,2,0,0,1,2,1,2}*C1:Cn^{0,0,1,2,1,1,2,2},TRUE,TRUE),
but I have difficulties to get the 3rd order with all the 16 termss
(Y = a0 + a1*x1 + a2*x1^2 + a3^x1^3 + b1*x2 + b2*x2^2 + b3*x2^3 + c1*x2*x1 + c2*x2*x1^2 + c3*x2*x1^3 + d2*x2^2*x1 + d3*x2^3*x1 + e2*x1^2*x2^2 + e3*x1^3*x2^2 + f2*x1^2*x2^3 + f3*x1^3*x2^3
using something similar:
LINEST(A1:An,B1:Bn^{1,2,3,0,0,0,1,2,3,1,1,2,3,2,3}*C1:Cn^{0,0,0,1,2,3,1,1,1,2,3,2,2,3,3},TRUE,TRUE).
The results do not seem to be correct, and don't know where I mixed up.
I hope all this discussions help others, too, as I spent a lot of time on the net trying to find info about this, and couldn't find exactly what I needed, except for your suggestions.
Thx,
Dan
RE: Multivariable regression in Excell
RE: Multivariable regression in Excell
These equations assume that your sheet has two named ranges: x and y.
Linear Trendline
Equation: y = m * x + b
m: =SLOPE(y,x)
b: =INTERCEPT(y,x)
Logarithmic Trendline
Equation: y = (c * LN(x)) - b
c: =INDEX(LINEST(y,LN(x)),1)
b: =INDEX(LINEST(y,LN(x)),1,2)
Power Trendline
Equation: y=c*x^b
c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))
b: =INDEX(LINEST(LN(y),LN(x),,),1)
Exponential Trendline
Equation: y = c *e ^(b * x)
c: =EXP(INDEX(LINEST(LN(y),x),1,2))
b: =INDEX(LINEST(LN(y),x),1)
2nd Order Polynomial Trendline
Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)
3rd Order Polynomial Trendline
Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
c3: =INDEX(LINEST(y,x^{1,2,3}),1)
c2: =INDEX(LINEST(y,x^{1,2,3}),1,2)
C1: =INDEX(LINEST(y,x^{1,2,3}),1,3)
b: =INDEX(LINEST(y,x^{1,2,3}),1,4)
Higher Order Polynomial Trendline
Notice the pattern in the two preceding sets of formulas
RE: Multivariable regression in Excell
thread770-184726: Excel Curve Fit Coefficients
RE: Multivariable regression in Excell
http://www.j-walk.com/ss/excel/tips/tip101.htm
RE: Multivariable regression in Excell
Cummings, the 2nd order polynomial, which has more than 7 coefficients, works, thus, computational power may not be the issue when talking about 16 coefficeints, for a 3rd order, 2 independent varaibles polynomial regression.
Kiwi and Clyde, thx for info, but what I need is multi-variable (let's say 2), not single variable regressions (e.g. I need: y = f(x1, x2). The info you provided is the base for the respective functions, but only for a single independent varable (y = f(x)).
The only one I still have problems for my "little exercis" is the 3rd order polynomial for 2 independent varables, with all the terms included in the equation (see a previous message).
Dan
RE: Multivariable regression in Excell
I did originally solve my problem as a 2nd order poly over two variables using a solver built into the 'ideas' process modelling environment, but I found that this was difficult to implement in excel (I had to solve it over and over for different chillers) and I found that the error was acceptable in approximating one of the dimensions as linear and adjusting after the first run.
Worth trying as you move in to the cubic realm.