Excel Regression Multivariable
Excel Regression Multivariable
(OP)
Hi,
I'm working in a worksheet that calculates all the regression types, linear and non-linear equations. But I'm using more than two independent variables(eg. X1,X2,X3,X4).I'm using the Excel formula =LINEST(), but I don't know how to introduces the information on "Knows_x's".
For the Multi variable Linear Regression I uses the following: =LINEST(Y,X1:X4,TRUE,TRUE) and I get the correct information. In the case of the Quadratic I don't know how to calculate. I found the following solution =LINEST(Y_1,X_1^{1,2}); but I don't know how to do it and I need to make this calculation with the Cubic,Power and Log regression.
I will appreciate your support on this.
Best Regards.
I'm working in a worksheet that calculates all the regression types, linear and non-linear equations. But I'm using more than two independent variables(eg. X1,X2,X3,X4).I'm using the Excel formula =LINEST(), but I don't know how to introduces the information on "Knows_x's".
For the Multi variable Linear Regression I uses the following: =LINEST(Y,X1:X4,TRUE,TRUE) and I get the correct information. In the case of the Quadratic I don't know how to calculate. I found the following solution =LINEST(Y_1,X_1^{1,2}); but I don't know how to do it and I need to make this calculation with the Cubic,Power and Log regression.
I will appreciate your support on this.
Best Regards.





RE: Excel Regression Multivariable
Or am I missing something?
RE: Excel Regression Multivariable
See Using LINEST for non-linear curve fitting
which has a spreadsheet for download with examples of all the available non-linear curve types.
If anything isn't clear, please ask.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Excel Regression Multivariable
IDS: Thank you. Actually I'm using that spreadsheet, but it has only one X's and that's the reason that I need to know how to do it with more that 2 X's.
RE: Excel Regression Multivariable
When you have a single X variable, say in A1:A10, then the formula (A1:A10)^{1,2} returns a two column range with x in the first column and x^2 in the second. You can use this in the regression formula, or enter it directly on the spreadsheet (using ctrl-shift-enter), and use that range in the regression formula.
I was about to say that this doesn't work with a multi-column range for x, but you can actually get it to work. If you have say X_1, X_2 and X_3 in range A1:C10, then copy that range to D1:F10, then you can use: =LINEST(Y_Range,A1:F10^{1,1,1,2,2,2}). It would be good if A1:C10^{1,1,1,2,2,2} returned a 6 column range, in the same way that (A1:A10)^{1,2} returns a two column range, but it seems it doesn't work that way.
There may be another trick to get this to work without any copying of data, but a quick search didn't find anything.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/