Excel Regression Output - How You Can Quickly Read and Understand It

hello and welcome to Excel Master Series comm today we're going to take a look at the four main parts of the output of an excel digression that will allow you to fully understand that regression in just a few seconds with just a glance you don't need to know every part of the output just the four main parts and you have a good idea of what's going on so let's take a look at a regression right here we have the tech index that is determined by the outputs of the two stocks the tech index is the dependent variable the Y variable of the regression equation and the two independent variables are the Google and Microsoft stock returns in those various states that will be the X variable of the regression equation now let's go ahead and regress those variables and we can see in Excel 2003 here's how we do it we go up to the drop down menu tools and we select the data analysis tool and then select regression of those choices hit okay and that brings up the regression dialog box the first thing we're going to do is check the residuals checkbox down at the bottom there we want to look at the residuals and then we're going to look at the input Y range that would be the dependent variable and we're going to select the tech index select all of the data along with the label the tech index label that's a dependent variable the Y variable now we're putting in the X variable range that would be the Google and the Microsoft stocks along with the labels and since we have the labels we're going to check that box and we're going to leave the 95% confidence interval box checked that's standard and the output range we're going to select the cell that will be the upper left-hand corner of the output of this regression that we sell 813 hit OK and run the regression we see that regression output starts and sell 813 and now here's the output and the first thing we're going to look at is the overall regression accuracy and that can be determined by r-square and adjusted r-square now our square represents the percent of variance of the output variable the tech index that's explained by the variance of the input variables that Microsoft will Google returns and we see in this case the r-square is not is point 907 that means ninety percent of the variance of the output variable is explained by the variance of the input variable adjusted r-squared is typically quoted more than R squared because it's usually more conservative and whenever you add a new input variable adjusted r-square only increases that that new input variable increases the predictive power of the regression equation R squared will always increase when you add a new variable and the second thing that we're going to look at is the probability that the regression output the output of the regression is not by chance and that would be shown by the significance of F of the regression the smaller the significance of F the greater the probability that the regression output is not by chance in this case significance of F for the regression is 0.02 vain that means there's only about a 2.8 percent chance that our output was obtained merely by random chance and the third part of this regression output that we're going to take a look at is the reliability of the regression lines coefficients and the y-intercept that would be determined by the p-values of each and we can see at the bottom of the chart the p-values are in purple and the intercept and the coefficients of the two variables are in yellow and the smaller the p-values the greater the probability that those outputs were not obtained by chance for example the p-value of Google is only 0.01 eight that means that there's only a 1.8 percent chance that that coefficient is obtained by chance and we can take those the intercept and the coefficients and create the regression equation right there as we've done now we're going to take a look at the residuals the residuals are the difference between the actual tech index the actual value of the dependent variable and the predicted value of the dependent variable that would be the residuals and we're going to graph those on a scatter plot chart and to do that in Excel 2003 go up to insert chart and then select scatter and we're just merely going to plant that scatter chart right there and take a look at it and what we want to see is that there is no pattern in the residuals they're centered around zero and they're somewhat normally distributed we see that so our regression appears to be okay no problem with the residuals as we can see here and the foremost of parts of most important parts of the output of regression down in Excel are number one the overall regression accuracy that would be determined by R square and adjusted r-square number two the probability that the regression output is not random if you look at the significance of F of the regression and number three the reliability of the regressions y-intercept and coefficients that can be shown by the p-value speech and by the residuals showing no patterns so if you'd like to be a an excel statistical master particular to the excel master series it's a for manual series you can pick it up at WWE Excel master series calm there it is selling 1995 for all four bucks thank you very much and goodbye

Channel: excelmasterseries

Views: 263,973

Rating: 4.8608694 out of 5

Keywords: excelmasterseries, excel master series, excel statistical master, regression, regression output, regression excel, r square, adjusted r square, r ssquared, p value, f statistic, residual, residuals, regression residual, regression residuals, excel statistics, excel graph

Id: ECXeUj8I6w8

Length: 5min 47sec (347 seconds)

Published: Thu Mar 18 2010