Linear Regression using Matrix in Excel

Feeder

Hey,

I have a college assignment on linear modelling I have to complete so I'm not looking for the full answer just a nudge in the right direction if possible.

The assignment document is attached but just to summarize where I am. I have used the values from the Table 2.1 to calculate the best fit values for the slope and the intercept in excel. See attached excel file !!

The next stage is where I am stuck I can find info about how to multiply matrices in excel and that seems OK but I'm not sure what that gives me and how it helps solve the final part of the assignment. In other words what does solving the normal equations Z]T[Z{A}={[Z]T{Y}} give me ?

Any help on this would be great as lecturers are on holidays and I would like to get it sorted in the next 2 weeks.

F.

sigmundv

Solving the normal equation for {A} gives you the optimal parameters of the linear fit, i.e. what is called "best fit values" in the assignment.

Feeder

Thanks sigmundv for the reply but did I not calculate the values for the A matrix in excel or am I missing something ?

I thought the {A} matrix was like shorthand for the a0 and a1 values I got from the intercept and the slope ? ? ?

sigmundv

Feeder wrote: »

Thanks sigmundv for the reply but did I not calculate the values for the A matrix in excel or am I missing something ?

I thought the {A} matrix was like shorthand for the a0 and a1 values I got from the intercept and the slope ? ? ?

Yes, {A} is a vector containing the two values [latex]a_0[/latex] and [latex]a_1[/latex]. They want you to calculate these values by solving the normal equation. I'm not sure how Excel does it. The idea behind the normal equation is that you can write [latex]a_0[/latex] + [latex]a_1 x = y[/latex] in matrix form like [latex][X]\{A\} = \{Y\}[/latex], where [latex][X][/latex] is an [latex]n \times 2[/latex] matrix with [latex]n[/latex] being the number of data points.

Does this make any sense to you?

Feeder

In the excel file attached I have a solution for getting the a0 and a1 values.

So if I already have these calculated then i do not need to use the Matrix formula.

I'm a little bit lost alright, is the matrix equation another method for solving for the a0 and a1 values ? ? ? ?

sigmundv

How did you calculate the [latex]a_0[/latex] and [latex]a_1[/latex] values? Did you just use the linear regression function in Excel?

You are right that the matrix equation is another way to find the [latex]a_0[/latex] and [latex]a_1[/latex] values, and in the assignment you are asked to find the values by solving this equation.

Feeder

Ha, I'm just here in my car on break looking at it again and just got the notice that you posted again.

I used a method from this website

http://easycalculation.com/statistics/learn-regression.php

I used excel just to tabulate the x and y values and then solved as per method above.

I have the excel file attached in the OP and it seems fine as I calculated it by hand also to verify.

If I have a0 and a1 does that mean so I have the (lnV0) and (1/r) values ???

Apologies I am on my phone so don't have a full keyboard

sigmundv

Thank you for the link. That would be one way of doing it, and in Excel it's easy to do, but in this assignment you are specifically asked to solve the matrix equation. You are right, when you have found a0 and a1 you have got ln(V0) and 1/tau, and from there you can easily find V0 and tau respectively.

Feeder

Thanks for the advice sigmundv.

I might email the lecturer to see what method he wants as the assignment says that I can use the method from notes which is similar to the link or the Excel method.

That's part 1 sorted so I think. Keep an eye out for the thread on Part 2 for non linear regression.

Again appreciate the help

F.

sigmundv

Getting clarification from the lecturer is a good idea. I'm happy to help and will look out for part 2 of your assignment.