1. ### Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. 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...

3. 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

Discussion in 'Ask an Excel Question' started by Havanai, Jun 11, 2018.

1. ### HavanaiNew Member

Messages:
8
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.
2. ### HuiExcel NinjaStaff Member

Messages:
11,374
Last edited: Jun 11, 2018
3. ### Peter BartholomewActive Member

Messages:
175
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.
4. ### Peter BartholomewActive Member

Messages:
175
@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
5. ### HuiExcel NinjaStaff Member

Messages:
11,374
The coefficients go from Mn, Mn-1, Mn-2, , , M2, M1, Intercept

from Excel Help
6. ### HavanaiNew Member

Messages:
8
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?
7. ### ChihiroExcel Ninja

Messages:
4,651
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.
8. ### HuiExcel NinjaStaff Member

Messages:
11,374
The process is important

Select the matrix area
Type formula and press Ctrl+Shift+Enter, Not just Enter
9. ### HavanaiNew Member

Messages:
8
@Hui
Thank you. Your diagnosis was correct. It worked.
10. ### Peter BartholomewActive Member

Messages:
175
@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.
11. ### HavanaiNew Member

Messages:
8
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?
12. ### HavanaiNew Member

Messages:
8
Do the se1 and se2 show the likely strength of the causal correlation of the x1 and x2 variables to the y-value?
13. ### HuiExcel NinjaStaff Member

Messages:
11,374
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
14. ### HavanaiNew Member

Messages:
8
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?
15. ### HavanaiNew Member

Messages:
8
@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?
16. ### HuiExcel NinjaStaff Member

Messages:
11,374
Thatâ€™s correct