Correlation and Regression with Excel

hi there in this demonstration we're going to see how to create scatter plots get correlation coefficients regression equations and regression lines all in Excel and we're going to do this without the analysis toolpak or any other third party ad in it comes with every version of Excel it's one of the statistical applications that Excel does best so suppose we have our our data we have the temperature in one column and the chirps rate of cricket chirps in the other right and suppose we just want the correlation coefficient what is the correlation coefficient well I come over here it is a function so I'm going to click on the FX button I know what it is but suppose you don't if you type in correlation and then hit go it gives you these options we want the core el cor arielle function open that up and now it asks for array 1 and array - those are just the two columns and with the correlation coefficient remember it doesn't matter which one you make X and which one you make Y or their correlation coefficient remains the same I notice also I'm not highlighting the column labels it needs numerical cells so that's a 1 a 2 2 a 3 a 13 and what are the chirps as array to hit ok and there's the correlation coefficient simple now suppose I want a scatter plot again this is really easy I'm just going to highlight both of these columns and I want temperature to be on the x-axis so I make sure the temperature is my first column and it already is if not you want to get your X variable on the left so I'll highlight that I'm going to go over to insert scatter I'll choose the first one right here and so that's sort of the default format or the default layout and there's other things you can do to make this look a little better if you go over to chart layouts right up here that is visible when you're clicked on the chart if you come out here it disappears but when you click on the chart chart layouts are there and I like this one over here it has x-axis label y-axis label and a title so I'll click on that and notice I have my two axis titles and my title so down here that was temperature so I'm going to click on there type in temperature down here I'm going to put in chirps for my y-axis chirps per minute good temperature and I don't really I can't really see them that well so I'm going to actually go in and I'm going to increase the font will go to 14 their temperature on the x-axis and 14 here and I don't really need this over on the right-hand side I would if I had more than one scatterplot going but I only have one so I no longer need that so I'll just click on it and hit backspace and it's gone I'll give it a title will say temp versus chirps there we go scatterplot looks great so now suppose I want to put in the regression line the line of best fit I just click on any one of these data points in the scatter plot and in fact you have to right-click and you get a little menu of options we'll go to add trendline the default is linear which is what we want we're going to display the equation on the chart and you may want to display r-squared on the chart but we'll do it for the demonstration purposes here I click OK notice it puts the line of best fit the regression line right in with the data it doesn't exceed it doesn't go beyond the data it just goes right through the data it looks very good and here's the regression equation and then the correlation coefficient squared which is this the coefficient of variation so we're going to do or than what we call the explained variation so I'm going to increase the font on that I'll bring that up to say 14 as well and maybe I'll even make it bold right that way you can really see it but I don't want that and we'll get it out of the way and in fact you know what you can do if these lines get getting in the way you can click on one of the lines backspace and they disappear so this now gives me my regression equation y equals four point oh six six nine x minus 200 four point two one and there it is that's your regression equation that's the variation the explained variation which is just this value of R squared and you've got a good-looking scatterplot with the regression line it's great simple easy it's one a few times in Excel where it does some statistical work really well and we're done

Channel: Scott Stevens

Views: 104,040

Rating: 4.8032789 out of 5

Keywords: Correlation, Regression, Excel, Statistics (Field Of Study)

Id: g_PH5FKBJJY

Length: 5min 31sec (331 seconds)

Published: Wed Aug 27 2014