• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Regression Y = M1X1 + M2X2 +B

Havanai

New Member
How do I perform a multiple linear regression? My X1 variable is in column fields E6:E20, X2 variable values are in F6:F20, and my Y values are in fields G6:G20. I would like to find the Y intercept B and the slopes M1 and M2, as well as other regression statistics.
 
One way or another it is going to involve the least squares approach to minimising the error. I assume you do not wish to do this from first principles, in which case installing the Analysis ToolPak should help.

https://support.office.com/en-us/ar...analysis-6c67ccf0-f4a9-487c-8dec-bdb5a2cefab6

The multiple regression appears to use the linear regression function LINEST, presumably as a step within a multivariate optimisation process. It is even possible to use Solver to minimise the least squares error to fit non-standard functional approximations.
 
@Hui
I have just had a look at your article, which I enjoyed. It is not obvious to me, simply from knowledge of array algebra, why LINEST returned the values it did (especially the coefficients in reverse order!)

Is this a case of a function that does far more than 'it says on the tin'?

Peter
 
The coefficients go from Mn, Mn-1, Mn-2, , , M2, M1, Intercept

from Excel Help
upload_2018-6-11_19-29-6.png
 
Select an area of 5 rows x 5 columns
Type =LINEST(G6:G20,E6:F20,TRUE,TRUE) and press Ctrl+Shift+Enter

The solution will be like
View attachment 52831

Then read the section on Multiple Variable linear regression at:
https://chandoo.org/wp/trendlines-and-forecasting-in-excel-part-2/

Before entering my question here, I had done some reading and I had already tried that LINEST function. But when I Ctrl-Shift-Enter it, I am not getting the table you show. I only get one number, -.0523 in my case. What am I missing?
 
I'd imagine you are not selecting required cell matrix to obtain result set.

Make sure you select 5x5 cell matrix, and press F2 to enter formula and confirm it with CTRL + SHIFT + ENTER.
 
The process is important

Select the matrix area
Type formula and press Ctrl+Shift+Enter, Not just Enter
 
@Hui
I should have read what it said on the tin rather than assuming I knew what it did! Some good has come of it; I now have taken a look at the Analysis ToolPak.
 
Select an area of 5 rows x 5 columns
Type =LINEST(G6:G20,E6:F20,TRUE,TRUE) and press Ctrl+Shift+Enter

The solution will be like
View attachment 52831

Then read the section on Multiple Variable linear regression at:
https://chandoo.org/wp/trendlines-and-forecasting-in-excel-part-2/
Okay, I have the two slopes m1 and m2 and the intercept b. Among the other values in the 5 X 5 grid, are there indicators of the "causal-strength" (maybe called coefficient of correlation?) that will show me if the x1 or x2 variable is strongly correlated with or determining the resulting y-value?
 
No it is the R2 value that determines the quality of the relationship

Low R2 is low correlation
High R2 is high correlation
Negative R2 is inverse relationship
 
Thanks again Hui. What is the range of R2? Is it -1 to +1? What would be considered "high"?
And is there an R1 for X1 variable and an R2 for X2 variable?
 
Thanks again Hui. What is the range of R2? Is it -1 to +1? What would be considered "high"?
And is there an R1 for X1 variable and an R2 for X2 variable?

@Hui
I don't think we can put sub-scripts or super-scripts here. I have been doing some reading. Is R2 the same as R-squared? Is it called the Coefficient of Determination? My value, with my data, is only .089 and I read that it ranges up to 100% or 1.0. So, I am interpreting my Coefficient of Determination to be quite low. Does that sound right?
 
Back
Top