Polynomial regression for multiple variables

**dirxess** · 03-15-2013, 03:07 PM

Dear forum,

When doing a polynomial regression with =LINEST for two independent variables, one should use an array after the input-variables to indicate the degree of the polynomial intended for that variable. I do not get how one should use this array. The array I am talking about is for example ^{2,1,0,0} in the expression =LINEST(E2:E16,A2:A16^{2,1,0,0}*B2:B16^{0,0,1,2} (link to a related thread)

What I would like to do is making a regression for two variables, one probably linearly and one second order related to Y. What I tried to do is:

=LINEST(X1, X2^{1,2},TRUE,TRUE)

for the intended model Y = A*X1 + B*X2^2 + C*X2 + D*X1*X2 + b

I thought that this would work, but the resulting LINEST-array gives me #value errors.

How would I use the ^{1,2,3,etc} array?

Thanks a lot!

**lecxe** · 03-15-2013, 05:25 PM

Hi
Welcome to the board

Originally Posted by dirxess

for the intended model Y = A*X1 + B*X2^2 + C*X2 + D*X1*X2 + b

For this model, assuming the range Y for the dependent variable, the range X_1 for the first independent variable and X_2 for the second, try:

=LINEST(Y,CHOOSE({1,2,3,4},X_1,X_2^2,X_2,X_1*X_2))

**dirxess** · 03-19-2013, 11:20 AM

It does work, even with the statistics for the accuracy of the regression! Thanks for this solution. On one hand it's nice to avoid the need for the array I mentioned earlier. On the other hand, I still don't get how the LINEST function really should work. However, I do think this method is flexible enough for my needs. Thank you!

If someone knows the answer, I'm still open for an explanation on the strange array in =LINEST(E2:E16,A2:A16^{2,1,0,0}*B2:B16^{0,0,1,2} (with the link to the original thread)

**MrShorty** · 03-19-2013, 12:47 PM

When I do these kind of regressions, I usually set them up like shg suggested in the linked thread -- build the matrix in the spreadsheet, then refer to the matrix in the LINEST() function. I guess that, with millions of cells at my disposal, I don't see any advantage to building complex arrays inside of the LINEST() function.

I sometimes think that the easiest way to understand what these arrays are doing is to actually put them in the spreadsheet and see. For example, understanding the * operator on arrays, I might set up two arrays, then add an array formula to an adjacent block of cells.

Please Login or Register  to view this content.

where column C2:C16 is array entered.

This spreadsheet will show you how I would begin to analyze that kind of array function.

**zerroTC** · 03-29-2013, 10:37 AM

I have explanation for you.
First of all we will observe two columns A and B. In column A we have numbers from 1 to 10 with step of 1 and on B from 10 to 100 with step of 10.

If you write A1:A10^{2;1;0;0} and with a standard method (choose a property range (4 col. and 10 rows) and ctr+shift+enter) execute this. You will get the matrix of 4 columns and 10 rows.
You will get something like this:

Please Login or Register  to view this content.

And now you write B1:B10^{0;0;1;2} and with a standard method (choose a property range (4 col. and 10 rows) and ctr+shift+enter) execute this. You will get the matrix of 4 columns and 10 rows.
You will get something like this:

Please Login or Register  to view this content.

And on last we have A1:A10^{2;1;0;0}*B1:B10^{0;0;1;2}.
And we will get this:

Please Login or Register  to view this content.

Better explanation in the picture below.
aha111.jpg

Now the right question is WHY in this format and WHY is only 4 colums?
The trick is in part of {0;0} on the bouth side.
The first column of the first matrix is multiplied with the first column of the second matrix, than the second column of the first matrix is multiplied with the second column of the second matrix, etc.
Simple as that.
P.S.
You can not have 0 data in your matrix becose 0^0 is error.
But you can put instead of 0, 1^-9 .There will be no error and on the other hand you will have the number very close to zero.
ZTC