Solve function in R in Excel

Question

Good morning,

So I'm doing some multiple linear regression, and need to show how to do it in Excel.

Basically, I've got three columns - Age, Weight, and Height as my x independent variables, and Waist Circumference as my y variable.

To set that up in R, so I'd get my coefficients, so I can plug them into a formula, and predict waist circumference for people with age, height, and weight, but no waist circumference:

X <- as.matrix(cbind(1, data$Age, data$Height, data$Weight))
y <- as.matrix(data$WaistCircumference)
beta_hat <- solve(t(X)%*%X)%*%t(X)%*%y
beta_hat
        [1]
[1] 100.5429
[2] 0.2136627
[3] 0.7744433
[4] -0.4450378

But I'd like to show how to do it in Excel as well. I think the formula would be something like this (as the solve function in R in that form is, I believe, supposed to give the inverse matrix of matrix X):

{=(MMULT(MMULT(MINVERSE(MMULT(TRANSPOSE(X),X)),TRANSPOSE(X)),Y))}

Where X is the array related to Age, Weight, and Height, and Y is the array related to Waist Circumference. So, using the cells on the sheet it looks like:

{=MMULT(MMULT(MINVERSE(MMULT(TRANSPOSE(A2:C7681),A2:C7681)),TRANSPOSE(A2:C7681)),D2:D7681)}

But that only gives me 3 values, and they aren't correct. Perhaps I'm doing order of operations incorrectly? I've tried moving around the MINVERSE as I thought that might be my problem, but that doesn't seem to fix it either. Any help would be appreciated :)

Edit: I've figured out how to do it - simply add a new column of all 1's to the X matrix, and use the above excel code, but include the new column in your X array, highlight four cells, type in the following formula, and hit contrl+shift+enter to enter an array formula:

{=MMULT(MMULT(MINVERSE(MMULT(TRANSPOSE(A2:D7681),A2:D7681)),TRANSPOSE(A2:D7681)),E2:E7681)}

Like so (it's, of course, much easier in R, but thought I'd share in case someone ever needs to do it in Excel):

Answer 1

Oh I got it! You just have to put a row of 1's down the side so you get a n x 4 matrix in Excel, then the equation above works! Sorry for posting! Should've tried one more thing haha