I want to show you how you can make a linear regression on Excel so what I'm going to do is just look at two variables I have my X variable which we think of as our explanatory variable and then I've got my Y variable it responds to that so that would be the interest rates going to explain the prob the median home price all right so we're going to be using a scatter plot a scatter plot requires two variables this will be my x-axis that'll be my wife axes so I highlight both of these variables I do not highlight the average that's not part of it insert you just go ahead you make your scatter plot alright so now I have a scatter plot and I want to try to make I figure out the correlation and the equation for this scatter plot so I've got some couple issues I would like to have axes labels a title I'd like to have a trendline there's a couple ways I can add these in I can use my chart elements and add in my title right here which actually have right there some 'soon axes title on the horizontal and an axis title on the vertical and I can just type in my correct one I can also add in my trend line right here it's linear so I can put that in here another option for doing that just so you know is also I can go to quick chart layouts and there actually is a chart layout right here that happens to have both a trendline and all the labels so I'm actually click on that and just use that I don't need that key right there I'm going to move this so I can actually see it I might also make it a little bit bigger so it's really clear all right so all right so now who's going to put that up hi I have an issue with the way this actually looks I have all this empty space and I can't leave focus on what's happening here so I'm gonna actually double click on this axis the x-axis and I realized that my data doesn't really start till about six so if it actually started at five my minimum was at five I could really see what's happening a lot better same with here I have a lot of empty space here so it doesn't need to start at zero if it started at something like a hundred thousand so I'm gonna just click over to here max these options and I'm going to set a minimum here at a hundred thousand okay and now I've really made it so that you can really focus on what is happening this is my trend line I'm gonna double click that trend line there and I will they want to be able to make that clears I might have it be actually a separate color okay so make it red I also want to see it thicker so it can really come through and I'd rather have it actually be a straight line rather than dashed so my graph now has a lot of information on it I forgot my I can see what my slope is I can see my intercept r-squared we'll deal with later I'll talk more about that and I can see if the line looks like it's definitely negatively sloped so what I want to now find is my correlation that's that measurement of strength one way to do it is to get all the z-scores and do the long way but a real quick way to do it is you simply type in coral okay coral and then coral you just highlight your x-axis comma then highlight your y-axis I'm going to come over here and highlight that explanatory variable comma then I'm gonna highlight my response variable and let's see how strong that relationship is so I end up with a negative relationship as I expected to make it easier to see them when I just go to just two decimal points so my correlation coefficient R is actually going to be negative point six two I can also find my slope and intercept I can look at them here but sometimes it's hard to be really accurate when you're looking at them there so I can equally have found them by just typing in slope double click on that and then here it tells you the Y's and then the X's so it actually tells me to put it in this order here comma for the Y and then I'm going to go over to my X's and there's my slope as you can see and for my intercept equals intercept exactly the same and again my Y's go in first as it actually tells you comma and then you put in your enter so we have the same numbers here as there this tells me that my slope so that would be that for every increase unit increase in the interest rate the actual price of the house will go down we can actually put the in dollars it will drop down by 20 over 23,000 dollars this all the intercept is telling me that if the interest rate so if this interest rate down here had been zero the price of the home would be three hundred and ninety three thousand well above that so these two are really good pieces of information about what's happening and I can see that I do have a somewhat strong relationship