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

    Havanai New 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. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,574
    Last edited: Jun 11, 2018
  3. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    427
    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 Bartholomew

    Peter Bartholomew Active Member

    Messages:
    427
    @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. Hui

    Hui Excel Ninja Staff Member

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

    from Excel Help
    upload_2018-6-11_19-29-6.png
  6. Havanai

    Havanai New 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. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,012
    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. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,574
    The process is important

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

    Havanai New Member

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

    Peter Bartholomew Active Member

    Messages:
    427
    @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. Havanai

    Havanai New 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. Havanai

    Havanai New 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. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,574
    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. Havanai

    Havanai New 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. Havanai

    Havanai New 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. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,574
    That’s correct

Share This Page