1 Equation for 4 variables

[afzalw]

Can I use excel to get make an equation for 4 variables (x,y,z,w)
e.g
a,b,c,d,3,f,g,h,i, would be constants

w = ax + by + cz + dx^2 + ey^2 + fz^2 + gxy + hyz + iyz
I tried regression but the equation looks like

w = ax + by + cz + c

Is it possible on ay other software e.g MATLAB

 

[shg]

Select a 10-cell wide range and array-enter

=LINEST(w, CHOOSE({1,2,3,4,5,6,7,8,9}, x, y, z, x^2, y^2, z^2, x*y, x*z, y*z))

The coefficients come out in reverse order: myz + ... + mx, and the offset is the rightmost value.

For example,

       --A--- --B--- --C--- --D--- --E--- --F--- --G--- --H--- --I--- ---J---
   1   3.1750 1.7404 1.2360 2.9508 1.9331 0.9725 4.3805 0.0000 2.1288 -3.6676
   2                                                                         
   3     x      y      z      w            Fit                               
   4       6      5      7    489           489                              
   5       7      3      6    365           365                              
   6       5      4      9    558           558                              
   7       4      4      7    384           384                              
   8       5      2      7    332           332                              
   9       1      6      9    542           542                              
  10       1      1      4     87            87                              
  11       6      0      5    192           192                              
  12       3      1      3     82            82

The formula in F4 and copied down is

=$I$1*x + $H$1*y + $G$1*z + $F$1*x^2 + $E$1*y^2 + $D$1*z^2 + $C$1*x*y + $B$1*x*z + $A$1*y*z + $J$1

 

[afzalw]

Thankyou I used =LINEST(w, CHOOSE({1,2,3,4,5,6,7,8,9}, x, y, z, x^2, y^2, z^2, x*y, x*z, y*z))
what should I use in place of x,y z in the above equation should I replace it with columns of x,y and z.
I tried doing it but I could not get the same answer as you got.
I used =LINEST(D4:D12, CHOOSE({1,2,3,4,5,6,7,8,9}, A4:A12, B4:B12,C4:C12, A4:A12^2, B4:B12^2, C4:C12^2, A4:A12*B4:B12, A4:A12*C4:C12, B4:B12*C4:C12))
and pressed ctl+ shift + enter for the same example but I could not get the answer.
Can you give me an excel file for an example.

 

[shg]

If you select the cells containing the data, with the header row at the top (as in the example), you can do Formulas > Defined Names group > Create from Selection, tick Top row, and write the formula exactly as shown. Or you can replace them with range references as you have done.

When you enter the LINEST formula, you have to select all 10 cells and enter the formula all at once; press and hold Ctrl and Shift, then press Enter.

 

[pgc01]

Afzalw, I agree with shg solution, just have a remark. I see that you are missing an independent term in your equation. If this is not just something you forgot, if you really want to impose that, then you can use the third parameter of Linest(), that will force it.

 

[shg]

What term are you referring to, pgc?

 

[afzalw]

Thankyou Shg it worked, it would sound strange but it worked when I used my keyboard left side ctrl and shift. Previously I was using right side ctrl and shift!
pgc01 it worked without including third and fourth parameter of LINEST

 

[shg]

Glad you got it sorted, you're welcome.

 

[pgc01]

What term are you referring to, pgc?

Hi shg

In your solution you assume that you can have an independent term, like

w = ax + by + cz + dx^2 + ey^2 + fz^2 + gxy + hyz + iyz + j

My question was:

- has afzalw forgot it in the equation he posted ?

or

- he really wanted to impose that there would be no independent term ?

If the latter case, then you could use the third parameter of Linest(), setting it to FALSE.

pgc01 it worked without including third and fourth parameter of LINEST

Hi. Of course it works, you can run a regression in either case, it just gives you a different solution. The question here is what is the model that you are using to explain the results. Should it or should it not include and independent term? Only you can know that since we have no idea of what you are calculating, just that you are using a quadratic form to explain the results.

Also it's only the third parameter. The fourth parameter does not change the calculation of the coefficients, just returns more stat info.

 

[shg]

Ah. I just assumed he omitted it inadvertently, like the duplication of the yz term.

Also, in my example showing the perfect fit, I should have had one more set of numbers (and still had a perfect fit), since there are 10 coefficients. That's why one of the coefficients in the example is 0.

Afzawl, do you get pgc's point -- do you want the offset term calculated, or want a solution where it must be zero? If the latter, the formula goes in only 9 cells:

=LINEST(w, CHOOSE({1,2,3,4,5,6,7,8,9}, x, y, z, x^2, y^2, z^2, x*y, x*z, y*z), FALSE)

And the reconstruction formula becomes

=$I$1*x + $H$1*y + $G$1*z + $F$1*x^2 + $E$1*y^2 + $D$1*z^2 + $C$1*x*y + $B$1*x*z + $A$1*y*z

Thanks, pgc.